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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
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:
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.
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.
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.