Filter Out Weekends and Weekday

This post will guide you how to filter out weekend or weekday from a list of data in Excel. How do I filter out the data in all Weekends in Excel. How to filter out all data in all weekdays in Excel.

Filter Out data in Weekdays and Weekends

If you want to filter out the data that belong to all the weekends or weekdays in Excel, how to achieve it.  You can use a formula based on the WEEKDAY function to return a number 1-7 for each day of the week.  The number 1 indicates that it is Sunday, and the number 7 indicates that it is Saturday.  Then you can use the Filter feature to filter out the data based on the week number. Just do the following steps:

#1 Type the following formula in a cell adjacent to the date column.


#2 press Enter key in your keyboard, and then drag the AutoFill Handle to other cells to apply this formula.

filter out weekday and weekend1

#3 keeping the week numbers are selected, and go to DATA tab, click Filter command under Sort & Filter group.

filter out weekday and weekend2

#4 click Arrow button in week number filed, checked 1 and 7 to filter out all weekends. Or checked 2-6 to filter out all weekdays. Click OK button.

filter out weekday and weekend3

#5 let’s see the result.

filter out weekday and weekend4

Related Functions

  • Excel WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…