Spreadsheet Practice

How to list the months between two dates in Excel

Excel formula

=DATE(YEAR(B1),MONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1)-1,1)

Count the months, then build the first of each with DATE: =DATE(YEAR(B1),MONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1)-1,1).

Result on the example
a 8-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

The years apart times 12, plus the months apart, plus 1 is the number of months the period touches: 8 from February to September. SEQUENCE returns 1 to 8, and DATE builds the 1st of each month by adding those numbers to the start month. DATE rolls month 13 into January of the next year, so the list can cross a year end.

Formatted as mmm yyyy, the cells read Feb 2026 to Sep 2026.

=DATE(YEAR(B1),MONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1)-1,1)

Result{8 rows × 1 col} 46054, 46082, 46113, 46143, …

  1. B12/15/2026

    The value in B1.

  2. YEAR(B1)2026

    YEAR: Year of a date.

    • serial_numberB12/15/2026
  3. MONTH(B1)2

    MONTH: Month of a date, 1–12.

    • serial_numberB12/15/2026
  4. B29/3/2026

    The value in B2.

  5. YEAR(B2)2026

    YEAR: Year of a date.

    • serial_numberB29/3/2026
  6. YEAR(B1)2026

    YEAR: Year of a date.

    • serial_numberB12/15/2026
  7. YEAR(B2)-YEAR(B1)0

    The - operator subtracts.

  8. YEAR(B2)-YEAR(B1))*120

    The * operator multiplies.

  9. MONTH(B2)9

    MONTH: Month of a date, 1–12.

    • serial_numberB29/3/2026
  10. YEAR(B2)-YEAR(B1))*12+MONTH(B2)9

    The + operator adds.

  11. MONTH(B1)2

    MONTH: Month of a date, 1–12.

    • serial_numberB12/15/2026
  12. YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)7

    The - operator subtracts.

  13. YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+18

    The + operator adds.

  14. SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1){8 rows × 1 col} 1, 2, 3, 4, …

    SEQUENCE: A grid of sequential numbers. Spills.

    • rowsYEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+18
  15. MONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1){8 rows × 1 col} 3, 4, 5, 6, …

    The + operator adds.

  16. MONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1)-1{8 rows × 1 col} 2, 3, 4, 5, …

    The - operator subtracts.

  17. DATE(YEAR(B1),MONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1)-1,1){8 rows × 1 col} 46054, 46082, 46113, 46143, …

    DATE: Builds a date from parts.

    • yearYEAR(B1)2026
    • monthMONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1)-1{8 rows × 1 col} 2, 3, 4, 5, …
    • day1

In Google Sheets

Google Sheets needs a different formula: =ARRAYFORMULA(DATE(YEAR(B1),MONTH(B1)+SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1)-1,1)). 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
=EOMONTH(B1,SEQUENCE((YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1)+1,1,0))spills 8 × 1Different resultEOMONTH gives the last day of each month instead, useful for month-end reports. Month-end dates: Feb 28 to Sep 30.

Common mistakes

  • DATEDIF(B1,B2,"m") counts only complete months, 6 here, so it misses the first or last month. Count from the year and month numbers instead.
  • Give the cells a month format such as mmm yyyy. With a plain date format, every entry shows as the 1st.

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.

List the months between two dates

In D2, list the first day of every month from the month of B1 to the month of B2, both included.

Questions

How do I list the month names as text?

Wrap the result in TEXT: =TEXT(DATE(…),"mmmm yyyy"). Text can't be used in date maths, so keep the dates if you'll sum by month.

How do I list the next 12 months from today?

Start from this month: =DATE(YEAR(TODAY()),MONTH(TODAY())+SEQUENCE(12)-1,1).

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.