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

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

**Excel:** `=EDATE(B1,SEQUENCE(B2,1,0))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(EDATE(B1,SEQUENCE(B2,1,0)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| First month | 2026-01-01 |  | Month starts |
| Months | 12 |  |  |

Formula in D2: `=EDATE(B1,SEQUENCE(B2,1,0))`

Result (spills 12 × 1): 1/1/2026; 2/1/2026; 3/1/2026; 4/1/2026; 5/1/2026; 6/1/2026; 7/1/2026; 8/1/2026; 9/1/2026; 10/1/2026; 11/1/2026; 12/1/2026

## 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.

## Other ways

- `=DATE(YEAR(B1),MONTH(B1)+SEQUENCE(B2,1,0),1)`: Builds 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.

## 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`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/list-the-first-day-of-each-month