To add dates that skip Saturdays and Sundays in Excel, enter your first date in A2, then enter =WORKDAY(A2,1) in A3 and fill the formula down. Excel returns the next weekday each time; you can also supply a holiday list so those dates are skipped too. Start with a real Excel date, not text, and decide whether your list should exclude public holidays.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The core WORKDAY formula works across these versions.
- A starting date and enough rows for the number of weekdays you need.
- Optionally, a range containing holiday dates to exclude, such as a list in column D.
Excel stores dates as numbers, even though it displays them in a calendar format. The formula can calculate with those date values and display the results as dates. If your starting value is left-aligned or does not respond to date formatting, check that Excel has recognized it as a date before continuing.
Step-by-Step
- Enter your starting date. In a blank worksheet, type the first weekday you want to appear into A2. For example, if your list begins on Monday, type that Monday’s date. Check that it is not a Saturday, Sunday, or holiday if the first entry must be a working day. You can label A1 “Weekday date” to make the column clear. If you need to retain the date you originally received, store it in a separate cell and use the next step to produce a normalized first date.
- Enter the next-workday formula. With the date in A2 and the next result intended for A3, type
=WORKDAY(A2,1)in A3 and press Enter. This asks Excel for the date one working day after A2. For example, if A2 contains Friday, March 6, 2026, the result in A3 is Monday, March 9, 2026. If the first date is already a weekday, A2 remains the first entry and the formula continues the sequence after it. - Fill the formula down. Select A3 and point to the small square at the lower-right corner of its border, called the fill handle. Drag it down to the last row you need. Excel adjusts the reference as it fills: the next row calculates from A3, the row after that from A4, and so on. Alternatively, select A3 and the blank cells beneath it, then use Home > Fill > Down. On Windows, Ctrl+D fills down; in Excel for Mac, use Command+D. Excel for the web may use a different browser or keyboard shortcut, so the ribbon command is a reliable option.
- Check the sequence at a weekend. Look at a Friday-to-Monday transition. Saturday and Sunday should not appear in the results. The dates still increase by one calendar day over ordinary weekdays, but increase by three calendar days from Friday to Monday. If Saturday or Sunday appears, check that you used WORKDAY rather than adding 1 to the previous date.
- Format the results as dates if needed. Select the date cells, then choose Home > Number Format and select Short Date or Long Date. You can also press Ctrl+1 on Windows to open Format Cells; on Mac, Command+1 opens the formatting dialog. Formatting changes how the date appears, not the underlying date value. Choose a format that makes the day and month unambiguous if the workbook will be shared across regions.
- Exclude holidays if your schedule requires it. Enter the holiday dates in a separate range, for example D2:D10, with one genuine Excel date per cell. With the previous date in A2 and holiday dates in D2:D10, replace the formula in A3 with
=WORKDAY(A2,1,$D$2:$D$10), then fill down again. The dollar signs keep the holiday range fixed as the formula copies. If a holiday falls on a weekday, Excel skips it as well as weekends. If there are no holidays to exclude, use the two-argument formula instead. - Choose the correct first date when your input might be a weekend. If a date supplied in B1 should become the first working date on or after that date, put the following in A2:
=WORKDAY(B1-1,1). With the supplied date in B1, this returns B1 when it is a weekday, or the next weekday when B1 is on a weekend. To include holidays in this first-date calculation, put holiday dates in D2:D10 and use=WORKDAY(B1-1,1,$D$2:$D$10). Then use the A2-to-A3 formula pattern from the earlier steps to continue the list. This keeps a weekend or holiday from appearing as the first item. - Use Excel’s weekday fill option for a quick fixed list. If you prefer not to use formulas and do not need a holiday list, enter the starting date, select the range to fill, and choose Home > Fill > Series. In the Series dialog, select Date under Type and Weekday under Date unit; set Step value to 1, then confirm. Excel fills the selected cells with weekdays and skips weekends. This is useful for a one-off list, while WORKDAY formulas are easier to audit and recalculate when the start date changes.
For a formula-based list, fill only as many rows as you need and keep the initial date visible. If you later change the starting date, the downstream formulas recalculate automatically. The direct fill method creates values rather than a linked formula sequence, so you may need to repeat the fill if the date range changes.
Common Mistakes
- Adding one calendar day instead of one workday. A formula such as
=A2+1includes Saturdays and Sundays. Use WORKDAY when the list must skip weekends. - Starting with a weekend date unintentionally. The first cell is not generated by the formula in A3, so it will remain whatever you typed in A2. Choose a weekday for A2, or calculate the first working date from a separate input as described above.
- Entering dates as text. A value like “next Monday” or a date with an apostrophe at the beginning may not be recognized as a date. Enter a date Excel can interpret, then apply a date format. Regional settings can affect whether 03/04 means March 4 or April 3.
- Letting the holiday range move. Without dollar signs, a relative holiday range shifts when you copy the formula down. Use absolute references such as
$D$2:$D$10, or select the holiday range and define a named range if the workbook needs a reusable list. - Expecting weekends to be the only nonworking days. WORKDAY skips Saturday and Sunday by default; it does not know your local public holidays or an organization’s closures unless you supply them.
- Filling the wrong cells. The formula belongs in the row after the starting date. If you fill A2 down after typing a date there, Excel may copy the date or create a different series rather than calculate the next workday.
Tips
- Keep the start date, generated weekday dates, and holiday list in clearly labeled columns. This makes the inputs easy to find and reduces the risk of editing a formula by mistake.
- Use a named range such as Holidays for a frequently reused holiday list. After defining the name in Excel, a formula with the prior date in A2 and the named holiday range can be written as
=WORKDAY(A2,1,Holidays). Check that the name refers to the complete holiday range. - For a standard Monday-to-Friday workweek, WORKDAY is the simplest choice. If your workweek uses different weekend days, Excel’s WORKDAY.INTL function can specify a weekend pattern. Confirm the weekend code or pattern string against your schedule before relying on it.
- When sharing a workbook, consider displaying dates with a four-digit year, such as 9-Mar-2026. This can prevent confusion between regional date conventions and make the year easy to verify.
- If you only need a static schedule, you can copy the generated cells and use Paste Special > Values to keep the displayed dates without the formulas. Keep a copy of the original formula-based range if you may need to change the starting date later.
- Excel for Mac uses the same formula and ribbon features for this task. Excel for the web also supports WORKDAY, though the ribbon layout and keyboard shortcuts can differ slightly; use the visible Home ribbon commands if a shortcut does not work.
Troubleshooting
The result is a number such as 46090. The formula may be returning a valid date serial that is formatted as General or Number. Select the cell and choose Home > Number Format > Short Date. The underlying value is still usable in calculations.
The formula displays #VALUE!. Check that the start-date cell contains a valid date value and that every entry in the optional holiday range is a date, not text. Remove stray spaces or labels from the holiday cells, and make sure the formula’s parentheses and commas match the examples.
The formula displays #NAME?. Check the function spelling: it is WORKDAY, with no space. In Excel installations configured for a different formula-list separator, your system may expect semicolons instead of commas; use the separator Excel inserts when you select the function in the formula bar.
The result skips a date you expected to include. Look for that date in the holiday range. A weekday listed as a holiday is deliberately skipped. Also check that the holiday dates do not include unintended cells, such as a heading that Excel has interpreted as a date.
Weekends still appear. Confirm that the formula is =WORKDAY(A2,1) and that the cell contains a formula rather than a typed value. Click a result and inspect the formula bar. If you used Home > Fill > Series, check that Date and Weekday—not Day—were selected in the dialog.
The fill handle is missing or difficult to use. Select the formula cell and use Home > Fill > Down after selecting the destination range. In desktop Excel, the fill handle can also be disabled in Excel Options under Advanced, in the editing options. If you are using the web app, check that the sheet is not protected and that you have permission to edit it.
Frequently Asked Questions
How do I add only weekdays in Excel?
Enter a starting weekday, then use =WORKDAY(A2,1) in the next row and fill down. WORKDAY advances one working day and skips Saturday and Sunday.
Can Excel skip holidays as well as weekends?
Yes. Put holiday dates in a range, then add that range as the third argument, for example =WORKDAY(A2,1,$D$2:$D$10). The holiday cells must contain date values.
Can I create a weekday list without a formula?
Yes. Select the destination range and choose Home > Fill > Series. Choose Date as the type and Weekday as the date unit, then set the step value to 1.
Does WORKDAY include the starting date?
No. =WORKDAY(A2,1) returns one working day after the date in A2. Keep the intended first date in A2, or use =WORKDAY(B1-1,1) when you want to turn an input date in B1 into the first weekday on or after it.
How do I use a workweek that is not Monday through Friday?
Use WORKDAY.INTL, which allows you to specify which days count as weekends. Check the function’s weekend argument for the pattern you need, and use a holiday range as its optional holiday argument where relevant.
Why does my date show as a number?
Excel stores dates as serial numbers. Apply a date format with Home > Number Format, such as Short Date, to display the value as a calendar date.
Related
- Google Excel Calendar Template: Find, Build, and Use One
- Google Sheets Excel Export Options: Formats and Steps
- Google Excel Timesheet Template: Sheets or Excel?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation Lists Based on CriteriaTo make an Excel drop-down list change according to a criterion, put a FILTER formula in a helper…
- Data Validation Multiple Selection in Excel: What WorksExcel’s built-in data validation list allows one selection per cell; it does not provide a native setting for…
- How to Close an Excel Sheet With a Keyboard ShortcutTo close the active Excel workbook in Microsoft 365 for Windows, press Ctrl+W. On a Mac, press Command+W.…
- Excel Protect Sheet Without a Password: What WorksYes. In Excel for Microsoft 365 on Windows, choose Review > Protect Sheet, set the permissions you want,…
- How to Autofit Rows in Excel With a Keyboard ShortcutIn Excel for Microsoft 365 on Windows, select the row or cells you want to adjust, then press…
- How to Add Turabian Footnotes in Word, Step by StepTo add a Turabian footnote in Word, place your cursor after the sentence punctuation, choose References > Insert…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.