Excel Filter Dates by Day of Week: Methods That Work

Formulas & functions · Updated November 19, 2026

Excel’s date filter menu does not include a direct day-of-week option, but you can filter weekdays by adding a helper column and filtering that column. With actual dates in A2:A100, enter =IF(A2="","",WEEKDAY(A2,2)) in B2 and fill it down; for March 7, 2025, the result is 5, which represents Friday. Then turn on filtering and select 5 in the helper column.

Why Excel’s date filter needs a helper column

Excel stores genuine dates as serial numbers, with any time of day represented as a fractional part. The date dropdown can group dates by periods such as years, quarters, or months, and it can filter a date range. It does not, however, offer a built-in checkbox for “Monday,” “Tuesday,” or another weekday across all dates.

A helper column gives Excel a weekday value it can filter like any other field. It also keeps the original dates intact, so you can still sort chronologically, create date-based summaries, and display the dates in their usual format. The method works with a normal range or an Excel table.

Filter weekdays with a helper column

In this example, column A contains dates and row 1 contains headers. The formula above returns a weekday number, leaving the helper cell blank when the corresponding date cell is blank. With the second argument set to 2, WEEKDAY numbers Monday as 1, Tuesday as 2, Wednesday as 3, Thursday as 4, Friday as 5, Saturday as 6, and Sunday as 7.

  1. Type a heading such as Weekday number in B1.
  2. Enter the formula in B2 and fill it down alongside the date records. Drag the fill handle, or copy the cell and paste it into the remaining rows.
  3. Click inside the data range. On Windows, choose Data > Filter. You can also use Home > Sort & Filter > Filter.
  4. Open the dropdown in the helper column, clear Select All, select the weekday number you want, and choose OK. For Fridays, select 5.

To show more than one weekday, select multiple numbers in the dropdown—for example, 6 and 7 for weekends. The filter hides nonmatching rows; it does not delete them. Clear the helper-column filter to show the full list again.

Use weekday names or numbers?

Numbers are compact and unambiguous when the helper formula uses WEEKDAY with a stated return type. They also avoid a possible sorting surprise: text labels may sort alphabetically, so Friday can appear before Monday rather than in calendar order. If you want a readable helper field, you can instead use a weekday-name formula, but the displayed language can depend on Excel’s regional settings. Number-based filtering is often the safer choice for a reusable workbook.

Remember that WEEKDAY’s numbering depends on its second argument. With 2, Monday is 1 and Sunday is 7. With 1, Sunday is 1 and Saturday is 7. If you omit the argument, Excel uses its default numbering, which may not match the Monday-first sequence you expect. State the argument explicitly so the helper values and your filter selections remain predictable.

The number is a calculation aid, not a replacement for the date. Keep the original date column available for users who need to see when each record occurred. You can label the helper clearly—for example, Weekday (Mon=1)—to explain why a Friday is represented by 5.

Keep the filtered records together

Apply the filter to the full table or data range, not only to the helper column. If Excel filters just one column while the other columns are outside the selected range, related values may not stay together as intended. A formatted Excel table is a convenient option: select the data and choose Insert > Table, confirming that the table has headers. Tables include filter dropdowns and generally extend to include new rows added directly below them.

If you add a helper column to a table, give it a header and enter the formula in its first data row. Excel tables commonly propagate a calculated-column formula to the other rows. Check that it has filled the records you expect, especially if the table has previously had formulas or rows removed. In an ordinary range, copy the formula to every relevant row before filtering.

Filtering changes which rows are visible, not the underlying data. When copying results, take care to copy only visible rows if that is your intention; simply clearing the filter later will reveal the rest of the records. For a separate, automatically refreshed results list, a dynamic-array formula may be a better fit.

Return matching rows with FILTER

Microsoft 365 for Windows includes the FILTER function, which can spill matching records into another area instead of hiding rows in the source list. This is useful when you want a separate Friday-only view while preserving the complete source data. It requires actual dates in column A, records across columns A:C, and no merged cells or existing content in the spill area.

With dates in A2:A100 and the complete records in A2:C100, enter =FILTER(A2:C100,(A2:A100<>"")*(WEEKDAY(A2:A100,2)=5),"No Friday rows") in an empty cell such as E2. It returns each nonblank row whose date is a Friday; if none match, it displays No Friday rows. Change 5 to another weekday number to return a different day. The source data must contain valid Excel dates, and the cells below and to the right of E2 must be clear for the results to spill.

FILTER is available in Microsoft 365 and Excel 2021 or later, but the formula shown is intended for Microsoft 365. Unlike the helper-column method, a spilled result is a separate output, not a filter applied to the original table. It updates when the source data changes, but you cannot edit individual returned cells as if they were the original records.

Prepare dates and handle time values

Before filtering, confirm that the date column contains real Excel dates rather than text that merely looks like a date. A quick clue is alignment: dates often align to the right by default, while text often aligns left, although formatting can change alignment. You can also select a cell and inspect the formula bar or temporarily change its number format. If the value does not behave like a date when sorted or formatted, convert it before relying on WEEKDAY.

Dates imported from another system may be text because of regional differences, such as a day-month order that Excel interprets differently from a month-day order. Do not assume that changing the display format converts text into a valid date. Use an appropriate import or conversion method for the source format, then check a few converted values against the original records.

Times do not normally interfere with weekday filtering. Excel’s date-time value includes a time fraction, and WEEKDAY returns the day associated with that date-time. Thus, a Friday appointment at 3:30 p.m. still returns 5 with the Monday-first numbering. If a value near midnight was imported in a different time zone, however, verify that its calendar date is the one your report should use.

Common problems and how to fix them

  • The weekday numbers look wrong: Check the second argument in WEEKDAY. The examples here use 2, so Friday is 5. If you used a different return type, the dropdown numbers will follow that alternative scheme.
  • Some records disappear unexpectedly: Check for blank dates, text dates, errors, or formulas returning empty text. The helper formula leaves genuinely blank date cells blank, but a text value that resembles a date may still cause a calculation error. Correct the source values before filtering.
  • The filter arrow is missing: Click inside the data and choose Data > Filter. If the range has blank rows or separate blocks, Excel may not identify the entire list. Select the full contiguous range first, or format the records as a table.
  • New rows are not included: In a normal range, expand the filter range and copy the helper formula into new rows. A table usually expands when records are entered directly beneath it, but verify that the new rows and formula are inside the table.
  • Weekday labels appear in an unexpected order: Text labels are typically sorted alphabetically. Use weekday numbers for filtering, or sort by the original date column when chronological order matters.

Also distinguish a weekday filter from a date-of-month filter. “Every Monday” means a recurring weekday across multiple months; “the 15th” means a calendar day number. Excel’s ordinary date filters may help with date ranges, but a weekday helper is the direct way to isolate recurring weekdays.

Excel for Mac and the web

The helper-column approach uses standard worksheet functions and works in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The exact appearance of the filter menus can vary by platform, but the core sequence is the same: calculate a weekday value, enable the column filters, and select the value you want. On Mac or in the browser, use the Data tab’s Filter command if the ribbon layout differs from the Windows steps above.

Dynamic-array support and interface details can vary with the Excel version and update channel. If FILTER is unavailable, use the helper-column method instead; it is compatible with older Excel versions that support WEEKDAY. In a shared workbook, keep the helper formula in the source table so collaborators can understand and maintain the filter.

Frequently Asked Questions

Can I filter Excel dates by weekday without a helper column?
Not with the standard date filter dropdown, which does not offer a weekday choice. Use a helper column for an in-place filter, or use FILTER in a supported version to create a separate results list.

How do I filter only Mondays in Excel?
Use the helper formula with WEEKDAY’s second argument set to 2, then filter the helper column for 1. With that numbering system, Monday is 1 and Friday is 5.

Why does WEEKDAY return a number instead of a name?
WEEKDAY is designed to return a numeric day-of-week code. With return type 2, its values run from 1 for Monday through 7 for Sunday; use those numbers directly in the filter dropdown.

Can I filter dates that include times?
Yes. A valid Excel date-time still has a weekday, and WEEKDAY returns that day even when the value includes a time. Check the calendar date if imported time-zone conversions could have shifted it.

Why does Excel treat my dates as text?
Imported values may not match the workbook’s regional date order, so Excel stores them as text instead of date serials. Convert them using a method that matches the source format, then confirm that sorting and date formatting behave as expected.

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.