How to filter weekdays and weekend days in Excel?

Tips and reminders:

• If your region starts the week on a different day, use =WEEKDAY(B2, 2) so Monday = 1 and Sunday = 7. Adjust filters accordingly.
• Blank cells in your date column may return unexpected results—clean your data if needed.
• Always drag the formula down to all rows to ensure complete filtering accuracy.

Notes:

• To filter weekdays only, check Mon, Tue, Wed, Thu, and Fri in Step 5.
• To preserve your original date values, consider copying them to a helper column before applying formatting and filtering.
• The Apply Date Formatting and To Actual tools are helpful when preparing clean, human-readable weekday data for reports or exports.

If you want to filter dates by weekdays or weekends without altering the original data format, the Super Filter utility of Kutools for Excel provides an efficient solution.

This method is ideal for users who need multi-condition filtering, prefer not to modify raw data, or require a graphical interface for repeated filtering tasks.

Step 1: Click Kutools Plus > Super Filter to enable the Super Filter pane in the workbook.

Step 2: In the Super Filter pane, perform the following actions:

1) Click the Browse button and select the data range you want to filter.

2) Hover over the OR label to expand the condition editor, then configure the filter criteria:

• Select the column name (e.g., Date) in the first box.
• Select Week in the second box.
• Select Equals in the third box.
• Select a number from 1 to 7 in the last box. For example, 1 = Sunday, 7 = Saturday.

Note: Numbers 1–7 represent Sunday through Saturday respectively. Adjust criteria if your week starts on a different day or uses different locale settings.

3) Click the Filter button to apply.

The data will now display only rows that match your specified day—for example, Sunday if you selected 1.

If you want to filter all weekends (Saturday and Sunday), add two conditions:

• Date > Week > Equals > 1
• Date > Week > Equals > 7

Ensure that the logic between them is set to OR.

To filter only weekdays (Monday through Friday), apply the following:

• Date > Week > Does not equal > 1
• Date > Week > Does not equal > 7

Make sure the logical operator between these conditions is set to AND.

Learn more about the Super Filter utility.

Use VBA Macro to Automatically Filter or Highlight Weekdays or Weekends

If you are working with large datasets, need automation, or frequently filter or highlight weekdays and weekends, using a VBA macro can save time and reduce repetitive manual work. This approach is suitable for advanced users or those familiar with Excel macros, and it eliminates the need for helper columns or third-party add-ins. Please ensure that macros are enabled and the workbook is trusted before proceeding.

Step 1: Press Alt + F11 to open the Microsoft Visual Basic for Applications editor. Then click Insert > Module and paste the following VBA code into the module window:

Sub FilterWeekendDates()
    Dim rng As Range
    Dim cell As Range
    Dim ws As Worksheet
    Dim weekDayNum As Integer

    Set ws = ActiveSheet
    On Error Resume Next
    Set rng = Application.InputBox("Select the date range to filter (single column, without header):", "Filter Weekends Only", Type:=8)
    On Error GoTo 0

    If rng Is Nothing Then Exit Sub
    If rng.Columns.Count > 1 Then
        MsgBox "Please select only one column.", vbExclamation
        Exit Sub
    End If

    Application.ScreenUpdating = False
    ws.Rows.Hidden = False

    For Each cell In rng
        If IsDate(cell.Value) Then
            weekDayNum = Weekday(cell.Value, vbSunday)
            If weekDayNum <> 1 And weekDayNum <> 7 Then
                cell.EntireRow.Hidden = True 
            End If
        Else
            cell.EntireRow.Hidden = True
        End If
    Next cell

    Application.ScreenUpdating = True
End Sub

Step 2: Close the VBA editor. To run the macro, press Alt + F8, select FilterWeekendDates from the list, and click Run. A dialog box will appear prompting you to select a single-column date range (excluding the header). The macro will then hide all rows that do not fall on weekends (Saturday and Sunday remain visible).

If you want to filter weekdays instead of weekends, please update the code line inside the loop as follows:

Filter Weekdays and Weekends Using Advanced Filter with Custom Criteria

Excel's Advanced Filter feature lets you filter data in place or copy results elsewhere using custom criteria, without needing helper columns or add-ins. By leveraging a custom formula based on the WEEKDAY function in the criteria range, you can flexibly display only weekdays, only weekends, or even specific days. This method is efficient for temporary, one-time filtering and avoids cluttering your worksheet with extra columns.

Follow these steps to use Advanced Filter for weekdays/weekends filtering:

• Step 1: Make sure your data has headers. For example, suppose your dates are in column B (header: Date).
• Step 2: Set up a criteria range elsewhere in your sheet. In the criteria range header cell (e.g., D1), enter the same header as your date column ("Date"). In the cell below it (e.g., D2), enter this formula to filter weekends:

  =OR(WEEKDAY(B2)=1, WEEKDAY(B2)=7)

• Step 3: Select your original data. Go to Data > Advanced in the Sort & Filter group.
• Step 4: In the Advanced Filter dialog, set:
  • List range: your original data, including headers
  • Criteria range: your criteria block (headers and formula cell)
  • Choose whether to Filter the list in place or Copy to another location
• Step 5: Click OK. Only weekend dates will be displayed (or copied, if that option was selected).

To filter for weekdays instead:

Use this formula in your criteria cell below the "Date" header:

=AND(WEEKDAY(B2)>1, WEEKDAY(B2)<7)

Demo: filter weekdays and weekend days in Excel

Best Office Productivity Tools

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
	Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
	Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
	Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

More details of Kutools for Excel... Free Download...

---

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!

More details of Office Tab... Free Download...

---

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

• All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
• One installer, one license — set up in minutes (MSI-ready)
• Works better together — streamlined productivity across Office apps
• 30-day full-featured trial — no registration, no credit card
• Best value — save vs buying individual add-in

More details of Kutools for Office... Free Download...