Spreadsheet Practice

How to create a list of the first day of each month in Excel

Excel formula

=EDATE(B1,SEQUENCE(B2,1,0))

Move the start date forward 0, 1, 2 … months: =EDATE(B1,SEQUENCE(B2,1,0)).

Result on the example
a 12-row list that spills from D2 in D2
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Different formula, see below

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

SEQUENCE(B2,1,0) makes the numbers 0 to 11, and EDATE moves the start date forward by each of them in months, giving the first of every month for a year. Change B2 for a longer or shorter list.

=EDATE(B1,SEQUENCE(B2,1,0))

Result{12 rows × 1 col} 46023, 46054, 46082, 46113, …

  1. B11/1/2026

    The value in B1.

  2. B212

    The value in B2.

  3. SEQUENCE(B2,1,0){12 rows × 1 col} 0, 1, 2, 3, …

    SEQUENCE: A grid of sequential numbers. Spills.

    • rowsB212
    • columns1
    • start0
  4. EDATE(B1,SEQUENCE(B2,1,0)){12 rows × 1 col} 46023, 46054, 46082, 46113, …

    EDATE: Date a number of months before or after.

    • start_dateB11/1/2026
    • monthsSEQUENCE(B2,1,0){12 rows × 1 col} 0, 1, 2, 3, …

In Google Sheets

Google Sheets needs a different formula: =ARRAYFORMULA(EDATE(B1,SEQUENCE(B2,1,0))). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=DATE(YEAR(B1),MONTH(B1)+SEQUENCE(B2,1,0),1)spills 12 × 1Builds each date from the year and a growing month number; DATE rolls month 13 into the next year.

Common mistakes

  • If B1 isn't the 1st, EDATE keeps its day of the month. Start from EOMONTH(B1,-1)+1 to be safe.
  • In Google Sheets, EDATE needs ARRAYFORMULA to run on the whole sequence.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Create a list of the first day of each month

In D2, list the first day of each month, starting with the month in B1, for the number of months in B2.

Questions

How do I list month ends instead?

=EOMONTH(B1,SEQUENCE(B2,1,0)).

How do I show them as Jan 2026, Feb 2026?

Format the spill range with the custom number format mmm yyyy.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.