Excel Formula for Date Sequence: 4 Easy Ways

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for a date sequence is =A2+1 entered in cell A3, assuming A2 holds your starting date, then dragging the fill handle down. For a dynamic sequence that updates automatically, use =SEQUENCE(10,,DATE(2025,1,1),1) in Excel 2021 or Microsoft 365. Below are four reliable methods, how to customize step and direction, and fixes for the most common problems.

Why You Might Need a Date Sequence

Date sequences appear in project schedules, amortization tables, attendance logs, and content calendars. Manually typing each date is slow and error‑prone, especially when you need 365 rows for a year or a list that skips weekends. A formula ensures every date is correct, consistent, and easy to change later. The method you choose depends on your Excel version, whether you want a static list or a dynamic array, and how much control you need over the step (daily, weekly, monthly).

Method 1: The Fill Handle (No Formula)

Although not a formula, the fill handle is the fastest way to create a date sequence and is worth knowing first. Type a start date in A2, then drag the small square at the bottom‑right corner of the cell downward. Excel continues the series, adding one day per row by default. To change the step, type two dates (e.g., 1/1/2025 in A2 and 1/8/2025 in A3), select both, then drag – Excel will repeat the difference. This works in all versions, including Excel for the web and Mac. It creates static values, so if you later change the start date, the generated dates do not update.

Method 2: Simple Addition Formula (Static)

If you prefer a formula that you can copy down, use addition. With your start date in A2, enter =A2+1 in A3 and fill down. Each row adds one day. For a weekly sequence, use =A2+7. For monthly, Excel’s date arithmetic gets tricky because months have different lengths – use =EDATE(A2,1) instead. This method is static: the dates are calculated once and remain fixed even if you change the start date later, because each formula references the cell above it. To make the whole list update when you change the start date, use the SEQUENCE method below or reference the original start cell in every row.

Method 3: SEQUENCE Function (Dynamic Array)

The SEQUENCE function, available in Excel 2021 and Microsoft 365, creates a dynamic array that spills into multiple cells automatically. The syntax is =SEQUENCE(rows, [columns], [start], [step]). For a list of 10 consecutive dates starting January 1, 2025, enter =SEQUENCE(10,,DATE(2025,1,1),1) in cell A2. Excel fills A2:A11 with dates from Jan 1 to Jan 10, 2025. The formula lives only in A2; the rest are part of the spill range. Change the start date or the number of rows, and the entire sequence updates instantly. For weekly dates, use =SEQUENCE(10,,DATE(2025,1,1),7). For a column of dates going backward, use a negative step, e.g., =SEQUENCE(10,,DATE(2025,1,1),-1). If you need dates in a row instead of a column, set columns to 10 and omit rows: =SEQUENCE(,10,DATE(2025,1,1),1).

Method 4: WORKDAY for Business Days Only

When you need a sequence of weekdays only (skipping Saturdays and Sundays), use =WORKDAY(start_date, 1). For example, with a start date in A2, enter =WORKDAY(A2,1) in A3 and fill down. This returns the next working day. To skip holidays as well, include a holiday range: =WORKDAY(A2,1,$F$2:$F$10), where F2:F10 contains holiday dates. The WORKDAY function is available in all modern Excel versions. For a dynamic array version, combine with SEQUENCE: =WORKDAY(DATE(2025,1,1),SEQUENCE(10)) – this spills 10 consecutive business days starting from the first working day on or after Jan 1, 2025. Note that WORKDAY treats Saturday and Sunday as non‑working days; if your weekend is different, use WORKDAY.INTL with a weekend code.

How to Choose the Right Method

  • Static list, one‑time use: Fill handle or simple addition (=A2+1).
  • Dynamic list that updates when start date changes: SEQUENCE (Excel 2021/365).
  • Business days only: WORKDAY or WORKDAY.INTL.
  • Monthly or yearly steps: EDATE for months, =DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)) for a more custom monthly step, or =DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)) for yearly.
  • Excel for the web or older versions: Fill handle or addition; SEQUENCE is not available in Excel 2019 or earlier.

Common Problems and Fixes

Dates show as numbers (e.g., 45658): The cell is formatted as General. Select the cells, press Ctrl+1 (Cmd+1 on Mac), go to the Number tab, choose Date, and pick a format.

SEQUENCE returns a #SPILL! error: Something is blocking the spill range. Clear the cells where the results would appear, or move the formula to a cell with empty space below/right.

Fill handle repeats the same date instead of incrementing: Hold down Ctrl (Cmd on Mac) while dragging, or type two dates first to establish the step.

Dates skip weekends unintentionally: If you used WORKDAY, that’s by design. Use simple addition if you want every calendar day.

Month‑end dates shift (e.g., Jan 31 becomes Feb 28): EDATE handles this correctly – it returns the last day of the target month, so =EDATE(DATE(2025,1,31),1) gives Feb 28, 2025. If you need the 31st of every month, use a formula that clamps to the last day, like =MIN(DATE(YEAR(A2),MONTH(A2)+2,0),DATE(YEAR(A2),MONTH(A2)+1,31)) – but that’s rare.

Frequently Asked Questions

How do I create a sequence of dates for every weekday in Excel? Use =WORKDAY(A2,1) in the cell below your start date and fill down. To skip holidays, add a holiday range as the third argument. For a dynamic array, =WORKDAY(DATE(2025,1,1),SEQUENCE(10)) gives 10 business days.

Can I make a date sequence that updates automatically when I change the start date? Yes – use the SEQUENCE function in Excel 2021 or Microsoft 365: =SEQUENCE(10,,A2,1) where A2 holds your start date. Change A2, and the whole list recalculates.

Why is my date sequence showing as numbers? The cells are formatted as General or Number. Select the range, press Ctrl+1 (Cmd+1 on Mac), choose Date under Number, and pick a format. The underlying value is still a date – it’s just displayed as a serial number.

How do I create a sequence of dates that skips weekends and holidays? Use =WORKDAY(start_date, 1) with a holiday range. For example, with start date in A2 and holidays in F2:F10, enter =WORKDAY(A2,1,$F$2:$F$10) and fill down.

Is there a way to create a date sequence without dragging? Yes – the SEQUENCE function spills automatically. Enter =SEQUENCE(100,,DATE(2025,1,1),1) in a single cell, and Excel fills 100 rows. This works only in Excel 2021 or Microsoft 365.

What is the difference between DATE and EDATE in a sequence? DATE builds a date from year, month, and day numbers. EDATE adds a number of months to a date, handling month lengths correctly. For monthly sequences, EDATE is safer because it avoids issues like Jan 31 to Feb 28.

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.