Create a Sequence of Dates in Excel: Formulas and Fill

Formulas & functions · Updated November 19, 2026

To create a sequence of dates in Excel, enter a starting date and use =SEQUENCE(10,1,DATE(2026,1,1),1) to spill ten consecutive dates down a column. For a simple list, you can also enter the first two dates and drag the fill handle; the best method depends on whether you need a changing, formula-driven list or a fixed set of dates.

Choose a method for your date list

Excel offers two practical approaches. The SEQUENCE function creates a dynamic array: one formula returns many dates, and the results update if you change the formula’s inputs. The fill handle and Fill Series commands create a conventional range of cells, which can be easier to edit individually or share with people using older Excel versions.

Use a formula when the number of dates or start date may change, or when you want a list that automatically expands or recalculates. Use Fill Series when you want a quick, static sequence or when the workbook needs to work in an Excel version that does not support dynamic arrays. Either method can create daily, weekly, or other regularly spaced dates.

  • Dynamic list: Use SEQUENCE in Excel for Microsoft 365, Excel 2021, or later.
  • Quick fixed list: Enter one or two dates and drag the fill handle.
  • Custom stop date: Use Home > Fill > Series to specify a step and an ending value.

How Excel stores and displays dates

Excel stores dates as serial numbers, rather than as words. A date such as January 1, 2026 is a number internally; the cell’s number format makes that value appear as a readable date. Adding 1 to a date advances it by one day, while adding 7 advances it by a week. This is why simple arithmetic and sequence formulas work for date lists.

When a result looks like a number instead of a date, the formula may still be correct. Select the result cells and choose Home > Number Format, then select Short Date or Long Date. You can also press Ctrl+1 on Windows to open Format Cells and choose a date format. On Mac, Command+1 opens Format Cells. Date formatting changes how a value looks, not the underlying date.

Create dates with the SEQUENCE function

In Excel for Microsoft 365 on Windows, click the cell where the list should begin, enter a formula, and press Enter. The formula returns an array that spills into the cells below it. For this example, assume the formula goes in A2 and you want ten dates starting January 1, 2026. The result runs from January 1 through January 10, 2026.

=SEQUENCE(10,1,DATE(2026,1,1),1)

The arguments mean the number of rows, number of columns, starting value, and step. Here, 10 rows and 1 column create a vertical list, and the step of 1 advances one day at a time. Use 1 row and 10 columns for a horizontal list. The DATE function supplies a reliable starting date without depending on how your computer interprets typed date text.

To make the count editable, put the start date in A2 and the number of dates in B2. Enter this formula in A4; it returns the number of daily dates specified in B2, starting with the date in A2.

=SEQUENCE(B2,1,A2,1)

In Microsoft 365 and Excel 2021 or later, the results spill automatically. Keep the cells below the formula clear. If any cell in the intended output area contains data, Excel may show a #SPILL! error; remove or move the obstructing content. A spilled array also cannot expand inside an Excel table, so place the formula outside the table or use a fill-based method there. Excel for the web and current Excel for Mac also support dynamic arrays, though ribbon layout can vary.

Use the fill handle for a simple sequence

For a sequence that does not need to update from one formula, type January 1, 2026 in A2 and January 2, 2026 in A3. Select both cells and drag the small square at the lower-right corner of the selection down the column. Excel recognizes the one-day pattern and continues it. Selecting two dates is especially helpful when you want a weekly or other repeating interval, because the gap between the examples tells Excel what to continue.

You can also enter a date in A2, then enter a formula in A3 to add one day. Assuming A2 contains January 1, 2026, this formula in A3 returns January 2, 2026. Copy it down for as many rows as needed.

=A2+1

To copy a formula down a selected range, select the starting cell and the cells below it, then press Ctrl+D on Windows. On Mac, use Command+D. The fill handle is often more intuitive for a short list; the keyboard command is convenient when you already know the range size.

Make weekly, monthly, and month-end sequences

To create a weekly list, use a step of 7. Assuming you want eight dates beginning Monday, January 5, 2026, and the formula is entered in A2, this returns Mondays through February 23, 2026.

=SEQUENCE(8,1,DATE(2026,1,5),7)

Monthly schedules need a little more care because months have different lengths. To keep a schedule on the same day of each month, use EDATE. With the formula entered in A2, this creates twelve monthly dates beginning January 15, 2026, including February 15 and March 15.

=EDATE(DATE(2026,1,15),SEQUENCE(12,1,0,1))

EDATE calculates each result from the original start date, so a start date near the end of a month can produce a shorter date in February and return to the original day in a later month. If you instead need the last calendar day of each month, use EOMONTH. Enter this formula in A2 to return twelve month-end dates from January through December 2026.

=EOMONTH(DATE(2026,1,1),SEQUENCE(12,1,0,1))

This distinction matters for recurring billing dates, reporting periods, and month-end checklists. A sequence that advances by a fixed number of days is not the same as a sequence that advances by calendar months.

List weekdays and working days

A weekday-only list excludes Saturday and Sunday. The WORKDAY function counts forward by working days, and in Microsoft 365 it can work with the array of day counts returned by SEQUENCE. Assuming the formula is in A2, this example returns the first ten Monday-to-Friday working dates beginning Monday, January 5, 2026.

=WORKDAY(DATE(2026,1,5)-1,SEQUENCE(10))

Subtracting one day from the starting date makes January 5 the first counted working day. To exclude holidays as well, put recognized holiday dates in F2:F10 and use the following formula in A2. The range can include blank cells; enter actual Excel dates for the holidays you want excluded.

=WORKDAY(DATE(2026,1,5)-1,SEQUENCE(10),$F$2:$F$10)

WORKDAY treats Saturday and Sunday as the weekend. If your organization uses a different weekend pattern, WORKDAY.INTL lets you specify it. Check that your holiday list contains real date values, not text that merely looks like a date, or the exclusions may not behave as expected.

Use Fill Series to set a step and stop date

Fill Series is useful if you want to control the interval without writing a formula. Enter a starting date, select the cells you want to fill, and choose Home > Fill > Series. In the dialog, select whether to fill down rows or across columns, choose Date as the series type, and set the date unit, step value, and stop value. For example, choose Day with a step of 7 to create weekly dates, or Month with a step of 1 for monthly increments.

The selected range determines where Excel fills, while the stop value can prevent the sequence from continuing past a chosen date. If you are unsure about the result, test the command in an empty range first. The menu names are generally similar in Excel for Mac, although the ribbon arrangement may differ by version. In Excel for the web, using the fill handle or a supported formula may be more straightforward than relying on the desktop dialog.

Fix common date-sequence problems

When a date list does not behave as expected, check the underlying values and the range before rebuilding it. These issues are common:

  • Numbers appear instead of dates: Apply Short Date or another date format. Excel may be showing the stored serial numbers correctly, but not in a readable format.
  • The formula returns #SPILL!: Clear cells in the intended spill area, unmerge any obstructing cells, and place the formula outside an Excel table.
  • Dates increase by the wrong interval: Check the final SEQUENCE argument. A step of 1 means one day; 7 means seven days. With Fill Series, check both the date unit and step value.
  • A typed date is treated as text: Enter it in a format Excel recognizes for your regional settings, or create it with DATE(year,month,day). A text value cannot always be used reliably in date arithmetic.
  • Monthly dates drift or skip the expected day: Decide whether the requirement is the same calendar day each month or the last day of each month, then choose EDATE or EOMONTH accordingly.

Regional settings affect how people type dates: a numeric date such as 04/05/2026 can mean different days in different locales. The DATE function avoids that ambiguity when you write formulas. If a workbook will be shared internationally, use a clear display format such as 5-Apr-2026 and make sure the cells contain date values rather than text.

Frequently Asked Questions

How do I automatically fill dates in Excel?

Enter a starting date and drag its fill handle, or enter a formula such as =SEQUENCE(30,1,DATE(2026,1,1),1) for 30 daily dates. The formula version updates as a single spilled list when its inputs change.

How do I create dates that skip weekends?

Use WORKDAY with a sequence of day counts. For example, a formula based on =WORKDAY(DATE(2026,1,5)-1,SEQUENCE(10)) returns ten working dates from January 5, 2026, excluding Saturday and Sunday. Add a holiday range as the third argument to exclude holidays.

Can Excel generate dates between a start date and an end date?

Yes. Work out how many daily entries are needed, then use that count as the first SEQUENCE argument and the start date as its starting value. If you need a specific ending date, confirm the count so the final result does not pass that date.

Why does my date formula show a number?

Excel dates are stored as serial numbers. Select the result cells and apply a date number format from Home > Number Format. The formatting changes the display, not the date value used in calculations.

Does SEQUENCE work in older versions of Excel?

SEQUENCE is available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual versions. If the function is unavailable, enter one or two dates and use the fill handle, or use Home > Fill > Series to create the date range.

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.