To fill an Excel column with weekdays, enter the first date, then enter =WORKDAY(A2,1) in the cell below it and fill that formula down. Excel will return the next Monday-to-Friday date each time, skipping Saturday and Sunday; you can also provide a holiday list to skip those dates.
How WORKDAY creates weekday dates
Excel stores dates as serial numbers, so date functions can calculate with them. The WORKDAY function moves forward or backward by a chosen number of working days. By default, it treats Monday through Friday as workdays and Saturday and Sunday as weekends. Setting the number of days to 1 advances to the next workday, even when that means moving across a weekend.
For example, if A2 contains Friday, March 6, 2026, the formula in A3 returns Monday, March 9, 2026. Copying the formula down produces March 10, March 11, and so on. The date in A2 is your starting point; the first result is the next weekday after it, not a repeat of the starting date.
“Weekday” can mean Monday through Friday, while “business day” often means a weekday on which an organization is open. Excel’s default weekend rule handles the first meaning. To account for holidays or closures as well, supply a list of those dates to the formula.
Enter the first date and fill down
- Enter the starting date in A2. For example, type
3/6/2026if you want to begin from Friday, March 6, 2026. Make sure Excel recognizes it as a date rather than text. - With the starting date in A2, enter this formula in A3:
=WORKDAY(A2,1). It calculates one workday after the date in A2. - With the formula in A3 and the starting date in A2, select A3 and the blank cells below it that you want to populate. Press Ctrl+D on Windows to fill down. On Mac, use Command+D. Alternatively, drag the fill handle—the small square at the lower-right corner of the selected cell—down the column.
Each filled cell adjusts its reference automatically: the formula in A4 uses A3, the formula in A5 uses A4, and so on. If you prefer the ribbon, select the formula cell and the cells below it, then choose Home > Fill > Down. Do not select A2 as the top cell of the fill range unless you intend to copy its date value down; the formula cell in A3 should be at the top of the selected range.
To create a fixed number of dates, fill only as many rows as you need. If you need dates through a particular end date instead, fill down far enough and check the last result, or use a separate end-date check. WORKDAY advances by workdays, so the number of calendar days covered will vary depending on how many weekends—and, if supplied, holidays—fall in the period.
Skip holidays as well as weekends
A weekday date is not necessarily a workday for your organization. If a schedule should omit holidays, put the holiday dates in a separate range, such as H2:H10. Keep them as real Excel dates; a text entry that only looks like a date may not be recognized. You can include dates beyond the visible schedule period if you want the list to be reusable.
With the current date in A2 and holiday dates in H2:H10, enter this in A3 and fill down: =WORKDAY(A2,1,$H$2:$H$10). The dollar signs keep the holiday range fixed as the formula is copied. Excel skips a listed holiday if it would otherwise land on that date. If a listed holiday falls on a weekend, it does not create an additional skipped weekday by itself; the weekend is already excluded.
Leaving out the holiday argument is appropriate when you simply need Monday-to-Friday dates, including public holidays. If your organization observes substitute days, add the actual observed dates to the list. Excel does not know which holidays apply in a country, region, or workplace, and it does not automatically move a weekend holiday to the nearest weekday.
Use a different weekend pattern
Some work schedules do not use Saturday and Sunday as weekend days. In that case, use WORKDAY.INTL, which lets you define the weekend pattern. Its argument order is the starting date, number of workdays, weekend setting, and optional holidays. With a nonstandard weekend, keep the holiday range fixed just as you would with WORKDAY.
For a schedule whose weekend is Friday and Saturday, with the current date in A2, enter this formula in A3 and fill down: =WORKDAY.INTL(A2,1,"0000110"). The seven characters in the weekend string represent Monday through Sunday; 1 means a weekend day and 0 means a working day. In this example, Friday and Saturday are excluded, while Sunday through Thursday are working days.
You can also use a numeric weekend code. For example, code 7 represents Friday and Saturday. The text pattern is useful when you want to see the weekday-by-weekday setting directly. Check the pattern carefully: it must contain seven characters in Monday-to-Sunday order. If you also need holidays, add the holiday range as the fourth argument.
Start on the first weekday on or after a date
The basic fill-down method assumes you enter the starting date yourself and want the next workday below it. A different requirement is to type any date, including a weekend, and have Excel return the first workday on or after that date. For example, if the input is Saturday, the result should be Monday; if it is already Monday, the result should remain Monday.
With the date to evaluate in A2, enter this formula in B2: =WORKDAY(A2-1,1). Subtracting one day before advancing by one workday makes the formula include A2 when it is already a workday. To extend that result into a list, use B2 as the starting date and fill the usual next-workday formula down from B3. If holidays matter, add the same holiday range to the formula and ensure that the input date is not itself a holiday.
Generate a list with a dynamic array
In Microsoft 365 and Excel 2021 or later, a dynamic array formula can return several weekday dates from one cell. This can be convenient when you want a fixed number of results without dragging or filling. It spills into the cells below, so those cells must be empty; otherwise, Excel displays a spill error.
With the starting date in A2 and a request for 10 following weekdays, enter this formula in B2: =WORKDAY(A2,SEQUENCE(10)). It returns ten consecutive workday dates after A2, with the first result one workday later. SEQUENCE creates the numbers 1 through 10, and WORKDAY calculates the date for each number. This does not include A2 itself. To include the starting date, place it separately or use a suitable starting-date setup before generating the remaining dates.
If the list must skip holidays, with those dates in H2:H10, use this version in B2: =WORKDAY(A2,SEQUENCE(10),$H$2:$H$10). Dynamic arrays are useful for a changing list, but a filled-down formula may be easier to inspect or more compatible with older workbooks. Excel for the web supports many Microsoft 365 functions, though the exact interface for filling cells can differ from the Windows desktop app.
Format and check the results
Excel may display a calculated date as a number if the destination cells use General or Number formatting. That does not necessarily mean the formula failed: the underlying result can still be a valid date serial. Select the output cells, then choose Home > Number Format and select Short Date or Long Date. You can also choose More Number Formats to select a custom display, such as a weekday name followed by a date.
Formatting changes only how a date appears, not the value used in calculations. For instance, a custom display can show the weekday name while the cell remains a real date that sorts chronologically and works in other formulas. Avoid building a schedule from text labels such as “Monday” alone if you need to compare dates or calculate durations; labels do not carry the same date value.
Check a few results around weekends and holidays. A Friday should be followed by Monday under the default weekend rule, and a listed holiday should be skipped if it falls on a weekday. If a result looks wrong, click the cell and inspect the formula bar to confirm the reference and any holiday range.
Common problems and how to fix them
- The formula returns a number. Apply a date format to the result cells. Excel stores dates as numbers internally, and formatting controls their visible appearance.
- The result is not a date. Check that the starting value is a real date, not text. Re-enter it in a date format Excel recognizes, or use the date picker if available in your workflow.
- Weekends appear in the list. Confirm that you used WORKDAY rather than adding 1 to the previous date. Ordinary addition counts calendar days and does not skip weekends.
- A holiday still appears. Confirm the holiday range is included, contains real date values, and covers the relevant year. Check that the formula uses the intended range and that the holiday is not recorded under a different observed date.
- Every filled row shows the same answer. If the formula uses an absolute reference to the starting date, such as
$A$2, every row may calculate from that fixed cell. For a step-by-step sequence, the reference to the preceding date should change as the formula is filled down. - A dynamic formula shows a spill error. Clear the cells below and to the right where the results need to appear. A merged cell or existing content in the spill area can block the array.
One common misconception is that Excel automatically recognizes every public holiday. It does not. WORKDAY skips Saturday and Sunday by default, or the weekend pattern you specify with WORKDAY.INTL; it skips holidays only when you supply their dates. That distinction matters for payroll calendars, delivery schedules, and service-level deadlines.
Choose between formulas and Excel’s fill series
Excel’s ordinary fill series can continue a sequence of calendar dates, but it does not by itself express the rule “skip weekends and these holidays.” WORKDAY makes that rule explicit and recalculates when the starting date or holiday list changes. It is generally the clearer choice for a reusable schedule.
For a one-off list of plain Monday-to-Friday dates, a formula filled down is quick and easy to audit. Use WORKDAY.INTL when the weekend differs from Saturday and Sunday, and add a holiday range when the schedule follows business closures. Use a dynamic array when your Excel version supports it and you want a fixed number of results to spill automatically. If you need a printed calendar with weekends visible, rather than a column containing only workdays, use a calendar layout or generate calendar dates and mark weekdays separately.
Frequently Asked Questions
How do I fill weekdays in Excel without weekends?
Enter a starting date, use =WORKDAY(A2,1) in the next row, then fill the formula down. WORKDAY skips Saturday and Sunday by default.
How do I include the starting date in the weekday list?
Type the starting date as the first entry, then calculate each following workday beneath it. The basic formula advances to the next workday, so it does not repeat the date already entered.
Can Excel skip holidays as well as weekends?
Yes. Put the holiday dates in a range and provide that range as WORKDAY’s third argument, or as the fourth argument in WORKDAY.INTL. Excel will not supply a holiday calendar automatically.
How do I make Friday and Saturday the weekend?
Use WORKDAY.INTL and set the weekend argument to the seven-character pattern "0000110", where the characters correspond to Monday through Sunday and 1 marks a weekend day.
Why does my weekday formula show a number?
Excel may be showing the date’s serial value with General or Number formatting. Change the cell format to Short Date or another date format; this changes the display, not the calculation.
Related
- Excel: Find Where a Cell Is Used Across Sheets
- Excel Custom Number Format for Millions
- Excel Custom Number Format: Show Numbers in Thousands
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete a Tab in Excel Without Losing Your DataTo delete a worksheet tab in Excel for Microsoft 365 on Windows, right-click the sheet tab at the…
- How to Insert a Full Line in Word: Borders and ShapesTo insert a full-width horizontal line in Word, put the cursor in the paragraph where the divider should…
- Google Sheets: How to Generate Unique IDsYes. Google Sheets can generate unique IDs with a row-based formula for simple lists, or with Google Apps…
- Excel Cell Repair in Clementi: Fixes and Local HelpIf you need Excel cell repair in Clementi, first identify whether the problem is a wrong formula or…
- Excel Number Format: Keep Leading Zeros in Your CellsYes. In Excel for Microsoft 365 on Windows, apply a custom number format such as 00000 to display…
- SUMIF Negative in Google Sheets? Alternatives That WorkGoogle Sheets can sum negative numbers directly with SUMIF: use the criterion "<0", as in =SUMIF(B2:B7,"<0",B2:B7). You usually…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.