How to Filter Out Weekend Dates in Excel: Step-by-Step

Formulas & functions · Updated November 20, 2026

To filter weekend dates out of an Excel list, add a helper column and enter =WEEKDAY(A2,2)>5 beside the first date, where the date is in A2. Fill the formula down, turn on AutoFilter with Data > Filter, then filter the helper column to show FALSE. This keeps Monday-to-Friday dates visible and hides Saturdays and Sundays without deleting any data.

What You’ll Need

  • An Excel workbook containing a list of dates, with one date per row. The instructions are for Microsoft Excel for Microsoft 365 on Windows.
  • A spare column beside the dates for the helper formula. If the list is an Excel table, you can add the helper as a new table column.
  • Dates stored as actual Excel date values, rather than text that only looks like a date. The formula method below can identify weekdays only when Excel recognizes the values as dates.

You do not need an add-in or a special date-filter setting. The helper column labels each row as a weekend or a weekday, and Excel’s regular filter then hides the rows you do not want to see.

Step-by-Step

  1. Check how your dates are arranged. Put a heading above the date column, such as Date, and keep each date in its own row beneath it. For example, if the heading is in A1 and the first date is in A2, the dates might continue down to A100. Keep other fields, such as names or notes, in neighboring columns so filtering can hide or show each entire record together. Avoid blank rows inside the list where possible.
  2. Add a helper-column heading. In the first empty column beside the date list, enter a heading such as Weekend? If dates are in A2:A100, place this heading in B1. A clear heading helps Excel include the helper values in the filter and makes it easy for you or a colleague to understand what the column means later.
  3. Enter the weekend test. With dates in A2:A100 and the helper heading in B1, enter this formula in B2: =WEEKDAY(A2,2)>5. The second argument, 2, numbers Monday as 1 through Sunday as 7. The comparison returns TRUE for Saturday or Sunday and FALSE for Monday through Friday. For example, a Saturday in A2 gives TRUE; a Wednesday gives FALSE.
  4. Fill the formula down the list. Select B2, then double-click the small square at the lower-right corner of the selected cell, called the fill handle. Excel usually copies the formula down alongside the adjacent dates. Alternatively, drag the fill handle to the last row or copy B2, select B3:B100, and paste. Check the last few rows to make sure every date has a corresponding result and that each formula refers to the date on its own row.
  5. Turn on AutoFilter. Click a cell inside the list, including its headings, and choose Data > Filter. Dropdown arrows should appear in the heading row. If Excel does not include the helper column, select the complete range, including all headings and data, before choosing Data > Filter. Do not select only the date column if you need the other fields to stay aligned with their dates.
  6. Hide the weekend rows. Open the dropdown in the Weekend? heading. Clear Select All, select FALSE, and click OK. Excel now shows the rows whose helper formula is FALSE—that is, weekdays—and hides rows marked TRUE. The original date values remain unchanged. Look at the visible dates to confirm that no Saturday or Sunday remains.
  7. Restore the full list when needed. Open the Weekend? dropdown again and choose Select All, then click OK. You can also use Data > Clear to clear filters from the current range. Clearing a filter shows the hidden rows again; it does not remove the helper formula or erase dates. To keep the weekdays-only view when you save, leave the filter applied.

For instance, suppose A2:A5 contain Friday, Saturday, Sunday, and Monday. The helper results are FALSE, TRUE, TRUE, and FALSE, respectively. Filtering for FALSE leaves Friday and Monday visible. If you need a separate weekday-only output rather than hiding rows in the original list, use the optional formula below in a blank area. It requires Excel for Microsoft 365 or Excel 2021 or later.

With valid dates in A2:A100 and a clear spill area for the results, enter =FILTER(A2:A100,(A2:A100<>"")*(WEEKDAY(A2:A100,2)<6),"No weekdays"). Excel returns the nonblank Monday-to-Friday dates as a spilling list. The condition excludes empty cells, and a range with no matching weekdays displays No weekdays. This formula creates a separate result; it does not hide rows in the source list. If you have other columns of details to return as well, adjust the first range to include those columns and use a matching condition range.

On Excel for Mac, the formula and filter approach are the same, and the Data tab contains Filter. The Windows shortcut for toggling filters is Ctrl+Shift+L; on Mac, use Command+Shift+F. In Excel for the web, use the Data tab’s Filter command or the heading dropdowns if filtering is already enabled. The appearance of some buttons can vary slightly by platform, but the helper-column values work the same way.

Common Mistakes

  • Using the wrong column reference. If your dates are in C2:C100, the formula in the helper column must refer to C2, not A2. The reference should change to C3, C4, and so on as you fill down. If every result looks identical, inspect a few formulas to see whether the reference was accidentally locked or points to another column.
  • Filtering for TRUE instead of FALSE. The formula labels weekends TRUE. To remove weekends from view, keep FALSE selected in the filter. Selecting TRUE does the opposite: it shows only weekend dates.
  • Sorting or filtering only one column. Applying a filter to just the dates can separate dates from the names or information in adjacent columns. Include the complete rectangular data range and its headings, or convert the list into a table, so each record stays together.
  • Entering dates as text. A value such as an imported date may look correct but be stored as text. Excel may return an error or an unexpected weekday result. Check that the date can be formatted as a date and that Excel recognizes it as a number, rather than relying on how it looks on screen.
  • Using the wrong weekday numbering. WEEKDAY(A2,2) makes Saturday and Sunday values 6 and 7. With the default numbering, Sunday is 1 and Saturday is 7, so a test written for one numbering system may not work with the other. Keep the 2 argument if using the formula shown here.

Tips

  • Make the range a table. Select a cell in the data and choose Insert > Table, then confirm that My table has headers is selected if your list has headings. Tables include filter dropdowns and usually extend formulas when you add rows. Keep the helper heading in the table so it is part of the same filterable data.
  • Choose a descriptive helper label. Weekend? works for a TRUE/FALSE result. You can instead use words for a more readable filter. With dates in A2:A100, enter =IF(A2="","",IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")) in B2 and fill down. This leaves the helper blank when the date cell is blank, and you can select Weekday in the dropdown. It assumes the date cells are valid dates or blank.
  • Keep a copy of the original data. A filter hides rows rather than deleting them, but a copy of the source list is useful before making larger changes. If you need to send a weekday-only list, copy the visible rows to a new sheet and check that the full records—not just the dates—were copied.
  • Remember that weekends are not the same as holidays. The formula treats Saturday and Sunday as weekends regardless of country, schedule, or business calendar. If your workweek differs, or you also need to exclude holidays, use a schedule-specific approach instead of assuming the standard Saturday-Sunday weekend.
  • Refresh your checks after editing dates. The helper formula recalculates when a date changes, but the filter view may need to be reapplied or refreshed to show the updated matching rows. If you add new records below an ordinary range, extend the filtered range; a table is often easier to maintain.

Troubleshooting

The formula returns #VALUE! or another error. Check that the referenced cell contains a real date and not text, an error, or an unsupported value. Try entering a known date directly into an empty cell and applying a date format. If imported dates are text, use Excel’s conversion tools or re-enter them as dates before relying on WEEKDAY. Also confirm that the formula points to the correct date column.

Saturday or Sunday is still visible. Open the helper-column dropdown and confirm that only FALSE is selected. Then check the formula in that row: it should refer to that row’s date and use the 2 argument. A formula copied with the wrong reference, or a row whose date is text, can produce a misleading result. If the helper shows TRUE but the row remains visible, clear and reapply the filter.

The filter arrows do not appear. Select a cell inside the complete list and choose Data > Filter. Confirm that the top row contains headings and that there are no completely blank rows splitting the range. If you use a table, check that the helper column is inside the table boundaries. In Excel for the web, make sure you have editing access and are not viewing a protected or read-only workbook.

New rows are not being filtered. An ordinary filtered range may not automatically include rows added beneath it. Extend the range to include the new rows, then fill the helper formula down. Alternatively, use an Excel table so added rows are more likely to become part of the same data range and inherit its formula and filter.

The dynamic FILTER formula shows an error or does not spill. FILTER is available in Excel for Microsoft 365 and Excel 2021 or later, but not in older perpetual versions. In those older versions, use the helper-column method. In supported versions, make sure the cells below and beside the formula are empty; a value blocking the spill range can cause #SPILL!. Check that the source range contains valid dates and that the formula’s ranges cover the same rows.

Frequently Asked Questions

Can I filter weekends without deleting them?
Yes. AutoFilter hides rows that do not meet the selected condition. Choosing FALSE in the helper column hides weekend rows from view but keeps the underlying records in the workbook. Clear the filter whenever you want to see them again.

Can I remove weekend rows permanently?
You can filter the helper column to TRUE, select the visible weekend rows, and delete those rows, but deletion changes the source data and may be difficult to undo later. Make a copy first and verify the selected rows before deleting. For most tasks, hiding weekends with a filter is safer.

How do I show only Saturdays and Sundays?
Use the same formula, =WEEKDAY(A2,2)>5, but select TRUE rather than FALSE in the helper-column dropdown. That displays weekend dates and hides weekdays. Clear the filter to restore the complete list.

Can Excel exclude weekends automatically in a separate list?
Yes, in Excel for Microsoft 365 or Excel 2021 and later, use the FILTER formula shown above to return weekdays to an empty area. The returned list updates as the source dates change. In older versions, filter the source list and copy the visible weekday records to another sheet.

Does this method exclude holidays too?
No. It checks only whether a date falls on Saturday or Sunday. A weekday holiday is still treated as a weekday. To exclude holidays, maintain a separate holiday list and add a holiday check to the logic, based on the dates and rules relevant to your calendar.

Will the formula work if my weekend is Friday and Saturday?
Not as written. The formula assumes the usual Saturday-Sunday weekend. For a different weekend, use a custom test based on the weekday numbers in your calendar, and confirm the numbering with WEEKDAY(A2,2), where Monday is 1 and Sunday is 7. Then filter the helper column using the labels your test returns.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.