Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

Easily sort dates by week/weekdays in Excel

Kutools for Excel's Advanced Sort utility provides us a quite easy way to sort cells by weekdays quickly as below screenshot shown. This utility also supports many flexible sorting methods, such as sort by text length, sort by last name, sort by absolute value, etc. Full Feature Free Trial 30-day!

ad sort by week

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • 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:

doc-filter-weekend-1

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:

doc-filter-weekend-2

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.

doc-filter-weekend-3

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

doc-filter-weekend-4

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:

doc-filter-weekend-8

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

doc-filter-weekend-9

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.     

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


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.

doc filter weekend 10

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

doc filter weekend 11

(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:

doc filter weekend 12

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:

doc filter weekend 13

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 - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Randomly insert only weekdays or weekends between two dates without duplicates

Kutools for Excel's Insert Random Data utility enables users to insert weekdays only or weekends only between two dates easily. What' more, this utility supports inserting unique weekdays or weekends by the Unique values option. Full Feature Free Trial 30-day!

ad insert random weekday weekends 2


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.