How to filter weekdays and weekend days in Excel?

Sometimes you may want to filter only weekends through a long date column, or to filter only the workdays. Most of time, it is not easy to recognize whether the date is a weekend or weekday when the date is in normal format styles, such as 06/07/2012. Here are two tricky ways to identify whether the date is a weekday or a weekend, and filter them easily in Excel.

Filter weekdays and weekends with functions

Filter weekdays and weekends with Kutools for Excel

Filter weekdays and weekend days with Super Filter utility of Kutools for Excel

Filter weekdays and weekends with functions

Microsoft Excel's Weekday function can identify a specific date, and returns a corresponding number from 1 to 7, the number indicates the date from Sunday to Saturday. For example, if the date is Sunday, it returns 1; if the date is Friday, it returns 6.

Step 1: In a blank cell, says the cell C2, enter the formula =WEEKDAY(B2), see screenshot:

Step 2: Then press the Enter key, and then select the cell C2, and drag the fill handle to the range that you want to apply this formula. See screenshot:

Step 3: And then you can click Data > Filter to filter the weekends or weekdays as you need.

Step 4: After clicking Filter, an arrow button will be appeared, click this arrow, and then check the 1 item and 7 item for filtering out weekends.

Step 5: Then click OK, and it filters out all weekends from the data range, and hides others. See the following screenshot:

Note: If you want to filter all weekdays except weekends, you can check the 2, 3, 4, 5, 6 items in this Step 4.

Filter weekdays and weekends with Kutools for Excel

In the first way, we need a help column to identify the date for filtering. However, we can format the date formatting style, and let the date shows as weekday. And Kutools for Excel 's Apply Date Formatting tool can help you deal with it easily.

Step 1: Select the date range that you will change date formatting styles.

Step 2: Click Kutools > Format > Apply Date Formatting, see screenshot:

Step 3: And In the popping up dialog box, select the Wed or Wednesday item in the Date format list.

Step 4: Then click OK, and the date has been converted to the week format, and then click Kutools > To Actual to change the date format to the text format.

Step 5: Click Data > Filter, and click the Arrow button, then check the Sat item and Sun item for filtering out weekends, see screenshot:

Step 6: Then click OK, and all weekends (Sundays and Saturdays) are filtered out, see screenshot:

Notes:
1. If you want to filter out all weekdays except weekends, you can check the Mon, Tue, Wed, Thu, and Fri items in this Step 5.

2. With this function, the original data may be modified, so you can copy the data to a helper column next to your data, and apply the features to the helper column.

Click to know more about these two features Apply Date Formatting and To Actual.     

Filter weekdays and weekend days with Super Filter utility of Kutools for Excel

If you want to filter dates by weekdays or weekend days without affecting original data formats, you can apply Kutools for Excel’s Super Filter utility to solve it easily.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

1. Click the Enterprise > Super Filter to enable the Super Filter pane in workbook.

2. In the opening Super Filter pane, please do as follows:

(1) Click the Browse button and select the data range that you will filter by weekdays or weekend days;

(2) Move mouse over the OR to display the filter criteria section, and:

• click the first box and select the column you will filter. In our example, we select the Date column;
• click the second box and select the Week from the drop down list;
• click the third box and select the Equals from the drop down list;
• click the last box and specify a number from the drop down list. See the left screen shot:

Note: when clicking the last box, the drop down list will show numbers from 1 to 7. The 1 means Sunday, 2-6 means Monday to Friday, and the 7 means Saturday.

(3) Click the Filter button.

Then you will see only the specified weekday (in our case it's the Sunday) are filtered out. See screen shot below:

If you want to filter all weekend days including Saturdays and Sundays, please specify both criteria Date (column name)> Week > Equals > 1 and Date (column name) > Week > Equals > 7, and make sure the relationship between both criteria is OR. See screen shot below:

If you want to filter all weekdays from Monday to Friday, please specify both criteria Date (column name) > Week > Does not equals > 1 and Date (column name) > Week > Does not equal > 7, and ensure the relationship between two criteria is AND. See screen shot below:

Click to know more about the Super Filter utility.        

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

Demo: filter weekdays and weekend days in Excel

Best Office Productivity Tools

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
	Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
	Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
	Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Read More... Free Download...

---

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download...