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.
In this tutorial, you will learn how to filter out weekdays and weekends among a list of dates. To filter the weekdays and weekends, you can use the excel built-in Weekday function. This function determines the day of the week for a given date. It returns a numerical value representing the day of the week, where Sunday is considered the first day of the week (represented by 1) and Saturday is the last day of the week (represented by 7).
Table of Contents
1. 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.
#3 keeping the week numbers are selected, and go to DATA tab, click Filter command under Sort & Filter group.
#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.
#5 let’s see the result.
2. Video: Filter Out data in Weekdays and Weekends
In this video tutorial, you will learn how to filter out weekdays and weekends among a list of dates using Weekday function and Filter feature in Excel 2013/2016/2019/365.
3. 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])…