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
- Excel Car Loan Amortization Template: Build Your Own
- Excel Calendar Template with Week Numbers?
- Excel Calendar Template with Notes? How to Make One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- PostgreSQL Call a Function: Syntax and ExamplesTo call a function in PostgreSQL, you use the SELECT statement with the function name and its arguments,…
- How to Filter Dates in Excel: A Step-by-Step GuideTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the dropdown…
- Conditional Median in Excel: Formulas and ExamplesIn Excel for Microsoft 365, calculate a conditional median with MEDIAN and FILTER: filter the numeric values to…
- UPPER Excel Formula: Capitalize Text in Excel CellsTo make every letter in text uppercase in Excel, use the UPPER function. If the original text is…
- End Behavior of Log Functions: Limits and ExamplesThe end behavior of a logarithmic function describes what its outputs do as the input moves toward the…
- Rank Low to High in Excel: Formulas, Sorting, and TipsYes. To assign ranks with the lowest value as rank 1, use Excel’s RANK.EQ function with its order…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.