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.

=WEEKDAY(B2)

#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])…
Related Posts
Get Employee Information by VLOOKUP

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today, in this article, we will show you the way to apply VLOOKUP to retrieve employee information. I hope this article will help you in ...

VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 ...

VLOOKUP with Multiple Lookup Values

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP Data by Date

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Workbook
VLOOKUP - Retrieve Data from Another Workbook 1

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Worksheet
VLOOKUP - Retrieve Data from Another Worksheet 3

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios ...

Basic Usage of INDEX & MATCH – Case Sensitive Lookup

In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match ...

How to Count Dates by Day of Week in Excel
count dates by days of week7

This post will guide you how to count days of week in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by day of week in Excel. Count Dates by ...

How to Sum Data by Weekday with Different Formulas/Functions in Excel
Sum Data by Weekday 20

To generate a business summary report for further investigation, we can sum income or profits for a date like Monday, Tuesday …etc. to check on which date we have the top income in a week, then we can estimate the ...

Sidebar