Excel Formula to List Only Weekdays in a Date Range

Formulas & functions · Updated November 19, 2026

With actual Excel dates in A2:A100, use =FILTER(A2:A100,IFERROR(WEEKDAY(A2:A100,2)<=5,FALSE),"No weekdays") to return only Monday-to-Friday dates from that list. If you want Excel to create a new sequence of weekdays instead, use the WORKDAY approach below.

Choose the formula for your task

“List only weekdays” can mean two different things: remove weekend dates from a list you already have, or generate a weekday-only schedule from a starting date. FILTER is suited to the first job because it selects qualifying values from an existing range and preserves their original order. WORKDAY is suited to the second because it calculates future working dates while skipping weekends.

The opening formula uses WEEKDAY with a return type of 2. That numbering makes Monday 1, Tuesday 2, and so on through Sunday 7, so values less than or equal to 5 are weekdays. IFERROR makes a cell that cannot be interpreted as a date fail the test rather than interrupting the whole result. The third FILTER argument supplies a friendly result when no dates qualify.

These are dynamic-array formulas: enter one formula in a single cell and Excel fills the results into nearby empty cells. They require Excel for Microsoft 365 or Excel 2021 or later. If you are using an older edition, the alternatives and limitations are covered below.

Filter weekdays from an existing date list

For the FILTER formula, the assumed layout is actual Excel dates in A2:A100, with any non-date cells either blank or values that should be ignored. Enter the formula in an empty cell outside that source range. It returns matching dates in the same order as the source; it does not sort them, remove duplicates, or fill in dates that are missing.

For example, if A2:A7 contains Friday, Saturday, Sunday, Monday, Tuesday, and Wednesday dates, the result contains Friday, Monday, Tuesday, and Wednesday. If the source list contains the same Monday twice, both copies appear. That is often useful when the dates are attached to transactions or appointments, because the formula does not silently change their sequence or remove records.

The source cells should contain real Excel date values, not text that merely looks like a date. Excel stores dates as numbers and displays them using a date format. Text dates can be interpreted differently depending on regional settings; IFERROR prevents some invalid values from breaking the result, but converting imported text to real dates first gives more dependable filtering. To check a questionable value, change its cell format temporarily to General: a real date normally displays as a serial number.

Because the output spills, keep the cells below the formula clear. If a value, merged cell, or another formula blocks the required space, Excel displays a #SPILL! error. Move the formula or clear the obstructing cells; do not copy the formula down row by row.

Generate a weekday sequence from a start date

To create new dates, the assumed layout is a start date in A2, a positive whole-number count of dates to return in B2, and optional holiday dates in D2:D20. Enter the following in an empty cell:

=WORKDAY(A2-1,SEQUENCE(B2),$D$2:$D$20)

SEQUENCE supplies the numbers 1 through the requested count, and WORKDAY turns those offsets into working dates. Subtracting one from the start date makes the first returned working date the start date itself when it is a working day. If the start date falls on a weekend or is listed as a holiday, Excel begins with the next working date. The holiday range is optional; omit the third WORKDAY argument if you do not need to exclude holidays.

For instance, if A2 is Monday, 6 January 2025, B2 is 5, and there are no holidays in the returned span, the result is 6, 7, 8, 9, and 10 January 2025. Saturday and Sunday are skipped. If 8 January is included in the holiday range, the fifth returned date moves to Monday, 13 January, because WORKDAY skips both the weekend and the listed holiday.

WORKDAY returns date serial numbers, so format the spill area as dates if Excel shows numbers instead. Select the output cells and choose Home > Number Format, then select a date format. B2 should be a positive whole number: a zero count does not produce a useful spill range, and a negative count is not appropriate for this forward-looking example.

Exclude holidays and adapt the weekend

WORKDAY treats Saturday and Sunday as nonworking days by default. Its holiday argument lets you exclude additional dates, such as public holidays or office closures. Put those dates in a range and use absolute references, as in the generated-sequence example, so the reference remains fixed if you copy or adjust the formula. Holiday entries should also be real dates; text entries may not match the date serials Excel is calculating.

If your normal weekend is not Saturday and Sunday, WORKDAY.INTL lets you specify a different pattern. For example, with the same start date in A2, count in B2, and holiday list in D2:D20, this formula uses Friday and Saturday as the weekend:

=WORKDAY.INTL(A2-1,SEQUENCE(B2),"0000110",$D$2:$D$20)

In a seven-character weekend string, the positions represent Monday through Sunday; 1 means a nonworking day and 0 means a working day. Thus 0000110 marks Friday and Saturday as days off. Check the pattern carefully before relying on it: a misplaced 1 can omit an ordinary workday or include a weekend date. WORKDAY.INTL also accepts built-in weekend codes, but the string makes an unusual weekly pattern explicit.

For an existing list, the opening FILTER formula only checks Monday through Friday. If your workplace has a different weekend, change the weekday test to match the days your schedule counts as working days, or use a helper column with a custom test. FILTER and WEEKDAY by themselves do not know your organization’s work calendar.

How weekday formulas treat dates

Excel does not store a date as a label such as “Monday.” It stores a number representing a date and applies a number format to display it as a calendar date. WEEKDAY evaluates that underlying value and returns a number for the day of the week. Choosing return type 2 is useful because the workweek maps neatly to 1 through 5, while Saturday and Sunday map to 6 and 7.

Displaying a date with a weekday name is different from filtering it. Formatting a cell to show “Monday” changes only what you see; it does not remove Saturday or Sunday from a list. Likewise, a formula that returns the word Monday is not a date value that can automatically be used in date arithmetic. Keep dates as dates when you need sorting, calculations, or comparisons, and use formatting to control their appearance.

Count weekdays instead of listing them

Sometimes the real question is how many weekdays fall between two dates, rather than which dates they are. With the start date in A2 and end date in B2, this formula counts working days inclusively, using the standard Saturday–Sunday weekend:

=NETWORKDAYS(A2,B2)

For example, from Monday, 6 January 2025 through Friday, 10 January 2025, the count is 5. NETWORKDAYS can also take a holiday range as an optional third argument. It returns a count, not a list, so use FILTER or WORKDAY when you need the individual dates. This distinction also helps explain why NETWORKDAYS may be useful for checking a schedule without being a replacement for a weekday-list formula.

Common problems and fixes

  • #SPILL!: The formula has nowhere to place one or more results. Clear cells in the spill area, unmerge any cells blocking it, or move the formula to an open area.
  • #VALUE! or unexpected results: Check for text in the source range, especially dates imported from another system. Convert text dates to actual dates and check that the cells use consistent regional date conventions.
  • Numbers instead of calendar dates: The results are probably valid date serials with a General number format. Apply a date format to the output range.
  • Holidays still appear: A FILTER test for weekdays excludes only weekend days. Add an explicit holiday test or use WORKDAY/WORKDAY.INTL when generating dates with a holiday range.
  • The first date is missing: Confirm that the start date is a working day and is not in the holiday list. WORKDAY deliberately skips a starting date that is nonworking.
  • Formula separators are rejected: The examples use commas as argument separators. Some regional Excel settings use semicolons instead; use the separator expected by your installation while keeping the function names and argument order intact.

Version and platform notes

FILTER and SEQUENCE are available in Microsoft 365 and Excel 2021 or later. WORKDAY and NETWORKDAYS are also available in older Excel releases, but the array-based sequence shown here depends on SEQUENCE and dynamic-array behavior. In an older version, you can enter a WORKDAY formula in the first row and fill it down, increasing the day offset on each row; that approach takes more setup and does not spill automatically.

In current Excel for Microsoft 365 on Windows, these formulas work as written. Current Microsoft 365 versions for Mac and Excel for the web also support the dynamic-array functions used here, though ribbon placement and date-format menus can differ slightly. If a workbook is opened in an older Excel version, dynamic-array formulas may not calculate as intended. For shared workbooks, check the recipient’s Excel version before depending on a spill formula.

Frequently Asked Questions

What is the simplest formula to remove weekends from a date list?
Use FILTER with WEEKDAY and return type 2 to keep dates numbered Monday through Friday. The formula at the top of this page also handles cells that produce errors and displays a message if no weekday dates are found.

How do I list weekdays between two dates?
For a new sequence, provide a start date and the number of working days you want, then use WORKDAY with SEQUENCE. If you already have every date in the interval, filter that existing range instead. This avoids generating a separate sequence when the source dates already exist.

Does Excel count Friday as a weekday?
Yes. With WEEKDAY’s return type 2, Friday is 5, so the test for values less than or equal to 5 includes it. Saturday and Sunday return 6 and 7 and are excluded.

Can the formula skip public holidays too?
Yes. Add a range of actual holiday dates to WORKDAY or WORKDAY.INTL when generating a sequence. For a pre-existing list, the basic FILTER test only removes weekends, so holidays need an additional condition if they should be excluded.

Why does Excel show a number instead of a date?
Dates are stored as serial numbers, and the formula result may inherit a General format. Apply a date number format to the result cells; changing the display format does not change the underlying date.

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.