To create a sequence of monthly dates in Excel for Microsoft 365, enter a starting date in A1, then use =EDATE(A1,SEQUENCE(12,1,0,1)) in an empty cell. The formula spills 12 dates, one month apart; format the results as dates if Excel displays serial numbers.
The basic formula and its result
In this example, A1 contains January 1, 2025. Enter the formula in B1, with the cells below it and the spill area clear. It returns the first day of each month from January through December 2025:
=EDATE(A1,SEQUENCE(12,1,0,1))
SEQUENCE creates the numbers 0 through 11. EDATE moves the date in A1 forward by each number of months. The first offset is zero, so the first result repeats the starting date; the next offsets add one month, two months, and so on. You can change 12 to the number of months you need. This formula uses SEQUENCE, which is available in Microsoft 365 and Excel 2021 or later.
For example, with A1 set to January 1, 2025, the results are January 1, February 1, March 1, and so on through December 1, 2025. Excel stores dates as serial numbers, so the underlying results are dates even if the cells initially show numbers.
How SEQUENCE and EDATE work together
SEQUENCE returns a spill range: one formula produces multiple results in neighboring cells. Its arguments specify the number of rows, number of columns, first value, and increment. In the example, 12 requests 12 rows; 1 requests one column; 0 is the starting offset; and 1 increases the offset by one each row.
EDATE is designed to move a date by a number of months. It takes a starting date and a month offset, so pairing it with a sequence of offsets creates regular monthly intervals without manually entering each date. The dates remain real Excel date values, which means you can sort them, use them in calculations, or compare them with other dates.
The first date is included because the sequence starts at zero. If you want the first result to be one month after the date in A1, start the offsets at 1 instead. For instance, with January 1, 2025 in A1, the formula =EDATE(A1,SEQUENCE(12,1,1,1)) returns February 1, 2025 through January 1, 2026. The number of results remains 12, but the range shifts forward one month.
Generate first-of-month dates
If the date in A1 might fall on any day of the month, but you specifically want the first day of each month, build each result from its year and month rather than preserving the starting day. With any valid date in A1, enter this in B1:
=DATE(YEAR(A1),MONTH(A1)+SEQUENCE(12,1,0,1),1)
If A1 contains January 15, 2025, this returns January 1, February 1, and subsequent month starts through December 1, 2025. DATE accepts month numbers beyond 12 and rolls them into the following year, so a starting date late in the year also works. For example, a sequence beginning in November continues into December and then January of the next year.
This approach is useful for reports whose columns or rows represent calendar months. Every result is normalized to day 1, avoiding an accidental sequence of dates such as January 15, February 15, and March 15 when the intended labels are month beginnings.
Set up the formula and format the results
- Enter a valid Excel date in a cell, such as A1. Typing January 1, 2025 or 1/1/2025 is usually recognized as a date according to your regional settings.
- Select a blank cell for the first result, such as B1, and enter the formula. Press Enter; in current Microsoft 365 and Excel 2021, the results spill automatically.
- Select the spilled dates and choose a display format. On Windows, use Home > Number Format, or press Ctrl+1 to open Format Cells. On Mac, the formulas work the same way; Command+1 opens Format Cells.
- Choose a date style such as 1/1/2025 or 1-Jan-2025. A custom format such as mmm yyyy displays Jan 2025 while retaining a real date value.
Formatting changes how a date appears, not the date stored in the cell. That distinction matters when you use a month sequence in a chart or calculation. A display such as Jan 2025 may look like text, but with a date number format the cell still contains a date. By contrast, converting dates to text can prevent normal date arithmetic and chronological sorting.
Month ends, same-day dates, and intervals
EDATE generally preserves the day number when that day exists in the destination month. When it does not, Excel uses the last day of that month. For example, from January 31, 2025, a one-month move produces February 28, 2025. In the original 12-month spilled formula, each result is calculated from the original start date and its offset, so a two-month move from January 31 produces March 31.
If you need the last calendar day of each month instead, use EOMONTH. With a date in A1 and the formula entered in B1, this returns the month ends from the starting month onward:
=EOMONTH(A1,SEQUENCE(12,1,0,1))
For A1 set to January 15, 2025, the results are January 31, February 28, March 31, and so on. EOMONTH also returns actual date values. It is handy for month-end reporting, but it is not the same as a sequence of dates on the same day of each month.
You can also make less frequent sequences by changing SEQUENCE’s step argument. With January 1, 2025 in A1, =EDATE(A1,SEQUENCE(4,1,0,3)) returns January 1, April 1, July 1, and October 1, 2025. This can represent quarterly starting months. Make sure the chosen number of rows and step match the period you intend; a three-month step does not itself calculate quarter totals or label fiscal quarters.
Options for older Excel versions
Excel 2019 and earlier do not have the SEQUENCE function. You can still create a monthly list with EDATE and fill a formula down. Suppose A1 contains January 1, 2025. Enter this formula in B2, then copy it down for as many months as needed:
=EDATE($A$1,ROWS($B$2:B2)-1)
In B2, ROWS returns 1, so the month offset is zero and the result is January 1, 2025. In B3, the range expands to B2:B3, ROWS returns 2, and the offset becomes one month. The next row advances the offset again. For 12 filled rows, the last result is December 1, 2025. The dollar signs keep the starting date fixed while the counting range grows as you copy down.
To make first-of-month dates with this older-version method, use the following formula in B2 and fill it down. With January 15, 2025 in A1, the first result is January 1, 2025, and each following row begins the next month:
=DATE(YEAR($A$1),MONTH($A$1)+ROWS($B$2:B2)-1,1)
Filling down is also a practical alternative when you want a conventional row-by-row list or need the results inside an Excel Table. Dynamic-array formulas spill into neighboring cells and cannot spill from within a table in the same way; put a spill formula outside the table, or use a copied-down formula in the table.
Common problems and fixes
- The cells show numbers: Excel dates are stored as serial numbers. Apply a date format with Home > Number Format or Format Cells rather than changing the formula.
- The formula shows #SPILL!: One or more cells in the intended output range may contain data, or the range may include merged cells. Clear the obstructing cells or move the formula to an open area. A spill formula also needs enough worksheet rows for every result.
- You see #NAME?: Check that the function is spelled SEQUENCE and that the Excel version supports it. SEQUENCE is available in Microsoft 365 and Excel 2021 or later; use the fill-down alternative in older releases.
- Dates appear in an unexpected order: Check the starting date and the SEQUENCE start and step arguments. A start of 1 skips the input month; a step of 2 advances every other month.
- The date typed in A1 is not recognized: Date interpretation depends on regional settings. Use a date Excel recognizes, or construct the starting date with DATE, for example
=DATE(2025,1,1).
When checking a result, select a cell and inspect the formula bar. A properly generated date may display as a month name, a short date, or a serial number depending on formatting, but the formula bar and number format help distinguish display from stored value.
Frequently Asked Questions
Can Excel automatically fill dates one month apart?
Yes. In Microsoft 365 or Excel 2021 and later, use EDATE with SEQUENCE to spill a monthly series. In older versions, enter an EDATE formula that counts rows and fill it down.
How do I create a list of month names instead of dates?
Keep the cells as dates and apply a format such as mmm yyyy or mmmm yyyy. This displays month labels while preserving dates for sorting and calculations. If you specifically need text, you can use Excel’s TEXT function, but text labels do not behave like date values.
How do I make every date the first of the month?
Use =DATE(YEAR(A1),MONTH(A1)+SEQUENCE(12,1,0,1),1) when the starting date is in A1. The final 1 sets the day of every result to the first, regardless of the day in the original date.
Why does January 31 become February 28?
February has no 31st day, so EDATE returns the last valid day of the target month. If you need month starts instead, use the DATE formula above; if you need month ends, use EOMONTH.
Can I create a monthly sequence in Excel for the web or on Mac?
The formulas are the same in Excel for the web and Excel for Mac when the version supports SEQUENCE. The main differences are interface details, such as keyboard shortcuts; on Mac, Command+1 opens Format Cells, compared with Ctrl+1 on Windows.
Related
- Excel Power Query: How to Change Close & Load Settings
- Excel PivotTable Dates: How to Ungroup Them Easily
- Excel Power Query Automatic Refresh: What to Set Up
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IF Function in Excel: Examples and Practical UsesIn Excel, the IF function checks whether a condition is true and returns one result if it is,…
- How to Capitalize Words in Excel: Shortcuts and FormulasExcel has no built-in keyboard shortcut that directly changes the case of selected text. To capitalize the first…
- Excel Column Subtraction Formula: Examples and TipsTo subtract values in one Excel column from the matching values in another, enter =A2-B2 in the first…
- Rank Values in Excel: Formulas, Ties, and Sorting ExplainedYes. In Excel for Microsoft 365 on Windows, use RANK.EQ to assign a rank to a value, or…
- Count Days in Excel: Formulas for Dates and WorkdaysTo count calendar days between two dates in Excel, put the start date in A2 and the end…
- Excel MONTH Function: Extract the Month from a DateTo get the month number from a date in Excel, enter =MONTH(A2) in another cell, assuming A2 contains…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.