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 60-day!

ad sort by week


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.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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 Excel tools. Full feature free trial 60-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.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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 Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


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 60-day!

ad insert random weekday weekends 2


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Screen Shot of Excel (with Office Tab installed)

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
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.