Autofill Weekday Dates in Excel and Skip the Weekends

How-to basics · Updated November 19, 2026

To autofill weekdays in Excel and skip Saturdays and Sundays, enter a starting date in A2, then put =WORKDAY(A2,1) in A3 and fill the formula down. For a quick one-time list, you can also drag the date’s fill handle and choose Fill Weekdays; the formula method is easier to update and can exclude holidays too.

Use WORKDAY to build a weekday list

The WORKDAY function returns a date a specified number of working days before or after another date. By default, it treats Saturday and Sunday as weekends. This makes it a dependable choice when a list must advance one business day at a time rather than one calendar day at a time.

  1. Enter the first date in A2. Make sure Excel recognizes it as a date, not text.
  2. With the starting date in A2, enter =WORKDAY(A2,1) in A3. The 1 means one working day after A2.
  3. Press Enter. If A2 is Monday, March 2, 2026, A3 returns Tuesday, March 3, 2026.
  4. Select A3 and drag its fill handle—the small square at the lower-right corner of the selected cell—down the column. Excel adjusts the reference for each row.

For that example, the next results are Wednesday, March 4, and Thursday, March 5. If the starting date is Friday, the next result is Monday, because the function skips the weekend. The starting date itself remains in A2; WORKDAY does not include it as the first step when you use a step value of 1.

You can fill the formula down as far as needed, or copy it into a selected range. The formula produces real Excel date values, so you can sort them, use them in calculations, or change their display format without converting them from text.

Use the fill handle without a formula

For a short, one-off list, Excel’s date autofill controls may be quicker. Type one valid date into a cell, select it, and drag the fill handle downward. After dragging, click the Auto Fill Options button that appears beside the filled range and choose Fill Weekdays. Excel then extends the dates while omitting Saturday and Sunday.

The initial drag may extend dates by calendar days, depending on the starting selection and Excel’s fill behavior. Check the dates before using the list, and select Fill Weekdays rather than assuming the default has skipped weekends. The Auto Fill Options button appears after a fill operation; if it is not visible, select the filled cells and use the formula or Series method instead.

Giving Excel two dates can make the intended pattern clearer. For example, enter Monday, March 2, 2026, in A2 and Tuesday, March 3, 2026, in A3. Select both cells and drag the fill handle down. Excel can continue the one-working-day pattern, but inspect the results, especially if the dates you entered are not consecutive weekdays. The WORKDAY formula is less dependent on Excel interpreting a pattern correctly.

Fill a weekday series from the ribbon

Excel for Microsoft 365 on Windows also has a Series dialog for filling a range. Enter the first date, select the range to fill including that starting cell, then choose Home > Fill > Series. In the dialog, set Series in to Columns for a vertical list or Rows for a horizontal one. Select Date as the Type, choose Weekday as the Date unit, and use a Step value of 1 to advance one weekday at a time. You can set a Stop value if you want the series to end at a particular date.

This method is useful when you want to fill a predetermined range without dragging, or when you prefer to choose the series settings before filling. The dialog changes the selected cells into a date series; it does not create formulas that recalculate from a linked starting date. If you later change the first date, use the formula method when you need subsequent dates to update automatically.

On Excel for Mac, ribbon and dialog placement can differ by release, and Excel for the web may not offer the same Series workflow. The WORKDAY formula works across these Excel versions, so it is a practical fallback when a menu command is not available or appears in a different place.

Exclude holidays as well as weekends

WORKDAY accepts an optional holiday list. Put actual Excel dates for holidays in E2:E10, with the starting date in A2, and enter =WORKDAY(A2,1,$E$2:$E$10) in A3. The dollar signs keep the holiday range fixed when you fill the formula down. Excel skips any date in that range in addition to Saturdays and Sundays.

For example, if A2 is Monday, March 2, 2026, and E2 contains Wednesday, March 4, 2026, the first result is Tuesday, March 3. The next result is Thursday, March 5: Wednesday is excluded because it appears in the holiday list. This is helpful for work schedules, delivery plans, and internal deadlines, but the holiday list must reflect the calendar relevant to your organization or location. Excel does not automatically know which dates are public holidays.

Keep the holiday cells as real dates and make sure the formula’s range covers the whole list. Blank cells in the referenced range do not count as holidays. If you add more dates later, extend the range in the formulas or reserve a larger range for the list. A misspelled or text-formatted holiday may not match the date value in the series.

Set a different weekend schedule

The standard WORKDAY function always skips Saturday and Sunday. For a schedule with different rest days, use WORKDAY.INTL, which lets you specify the weekend pattern. For example, with the starting date in A2 and holidays in E2:E10, =WORKDAY.INTL(A2,1,7,$E$2:$E$10) uses weekend code 7, meaning Friday and Saturday are nonworking days. The result advances one working day and also skips the listed holidays.

Weekend codes are useful for shift patterns or calendars where the usual Saturday–Sunday weekend does not apply. Check the code before filling a long schedule: changing the weekend definition changes which dates Excel treats as working days. If your workweek is the ordinary Monday through Friday, use WORKDAY instead; it is simpler and makes the default weekend rule clear.

Make sure Excel is treating your entry as a date

Excel stores dates as serial numbers and applies a date format to display them as calendar dates. A cell that looks like a date may instead contain text, particularly if it was pasted from a website or imported from another system. If WORKDAY returns an error or the fill handle repeats the same entry, check whether Excel recognized the starting value as a date.

  • Enter the date in a format Excel recognizes under your regional settings, or use the calendar control if one is available.
  • Change the cell format using Home > Number Format and choose a date display if the result appears as a number.
  • Check for leading apostrophes or spaces, which can make a date entry text rather than a date value.
  • Use a clear display such as 2-Mar-2026 when you want to reduce confusion between regional day-month and month-day formats.

Formatting changes how a date looks, not the underlying date value or the sequence logic. A result displayed as 3/3/2026 and one displayed as 03-Mar-26 can represent the same date. If a cell shows a number such as 46084, that may be a valid date serial with a General or Number format; apply a date format to display it as a calendar date.

Choose the method that fits your schedule

Use the fill handle and Fill Weekdays for a quick list that you do not expect to maintain. Use the Series dialog when you want to fill a chosen range with a weekday sequence in one operation. Choose WORKDAY when later dates need to recalculate after the start date changes, or when you need a maintained holiday list. For nonstandard weekends, WORKDAY.INTL provides the extra weekend setting.

A common misconception is that entering a date and dragging always skips weekends automatically. Excel may continue by calendar day unless you select the weekday fill option or supply a weekday pattern it can infer. Another tempting approach is to add 1 to the previous date; that adds one calendar day and therefore includes weekends. For a workday schedule, use a method that explicitly handles nonworking days.

Also distinguish between a fixed schedule and a live date. A formula based on a date entered in A2 preserves the chosen start date until you change it. A list based on the current date would move as the workbook recalculates, which may not be appropriate for a published timetable. Decide whether you want a record of dates already planned or a schedule that shifts with a changing reference before choosing how to generate the list.

Frequently Asked Questions

How do I autofill weekdays in Excel without weekends?
Enter a starting date, then use the fill handle and choose Fill Weekdays from Auto Fill Options. For a formula-based list, with the starting date in A2, enter =WORKDAY(A2,1) in A3 and fill down.

Does Excel automatically skip weekends when I drag a date?
Not always. A normal date fill can advance by calendar days. Choose Fill Weekdays from Auto Fill Options, use the Series dialog’s Weekday date unit, or use WORKDAY so the weekend rule is explicit.

How can I skip holidays too?
List the holiday dates in cells and include that range as WORKDAY’s third argument. With the start date in A2 and holidays in E2:E10, enter =WORKDAY(A2,1,$E$2:$E$10) in A3 and fill down.

Why is Excel showing a number instead of a date?
Excel may be displaying the date’s serial value with a General or Number format. Select the cells and choose a date format from Home > Number Format. The underlying value can still be a valid date.

Can I use a weekend other than Saturday and Sunday?
Yes. Use WORKDAY.INTL and specify a weekend code or pattern. For example, weekend code 7 treats Friday and Saturday as the weekend; check the code against your actual schedule before filling the list.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.