Sequence of months

The goal is to generate a series of dates by month based on a given start date. In the current version of Excel, the easiest way to solve this problem is to use the SEQUENCE function inside the EDATE function like this:

=EDATE(B5,SEQUENCE(12,1,0))

The result is a series of 12 dates, incremented by one month, beginning on June 1, 2023, and ending on May 1, 2024. At a high level, this formula uses the EDATE function to return 12 dates one month apart, and it uses the SEQUENCE function to create the numeric array needed to perform this operation in one step.

EDATE function

The EDATE function moves forward or backward in time in one-month increments from a given start date. The generic syntax for EDATE looks like this:

=EDATE(start_date,months)

Inside EDATE, start_date is any valid Excel date, and months is the number of months to add or subtract from start_date. For example, with the date June 1, 2023, in cell B5, EDATE works like this:

=EDATE(B5,-2) // returns April 1, 2023
=EDATE(B5,-1) // returns May 1, 2023
=EDATE(B5,0) // returns June 1, 2023
=EDATE(B5,1) // returns July 1, 2023
=EDATE(B5,2) // returns August 1, 2023
=EDATE(B5,3) // returns September 1, 2023

The challenge in this problem is that we want to return 12 dates at the same time. One way to accomplish this is to use the SEQUENCE function to generate a numeric array we can plug into EDATE.

For more details on EDATE, see: How to use the EDATE function.

SEQUENCE function

The SEQUENCE function is designed to generate numeric sequences in rows and/or columns. The generic syntax for SEQUENCE looks like this:

=SEQUENCE(rows,[columns],[start],[step])

In the example shown, we use SEQUENCE to generate 12 sequential numbers like this:

SEQUENCE(12,1,0)

Here, rows is 12, columns is 1, and start is 0. The reason we want this array to begin with zero is that we want to include the start date in the final result. With this configuration, SEQUENCE will return an array like this:

{0;1;2;3;4;5;6;7;8;9;10;11}

This array is then provided to the EDATE function as the months argument, as described below.

For more details on SEQUENCE, see How to use the SEQUENCE function.

EDATE + SEQUENCE

This array created by SEQUENCE is returned as the months argument inside the EDATE function:

=EDATE(B5,{0;1;2;3;4;5;6;7;8;9;10;11})

EDATE then returns 12 dates, beginning with the date in B5. Because Excel dates are stored as large serial numbers, the result is an array like this:

{45078;45108;45139;45170;45200;45231;45261;45292;45323;45352;45383;45413}

This array lands in cell D5 and spills into the range D5:D16. When this range is formatted with date formatting, Excel will display 12 dates beginning on June 1, 2023, and ending on May 1, 2024.

End of month

To generate a series of "end of month" dates, you can use the EOMONTH function instead of EDATE like this:

=EOMONTH(B5,SEQUENCE(12,1,0))

EOMONTH works just like EDATE, except it always returns a date at the end of the month, regardless of the date provided.

Month names only

To generate a list of month names (instead of actual dates), you can wrap the formula above in the TEXT function:

=TEXT(EDATE(B5,SEQUENCE(12,1,0)),"mmmm")

The TEXT function will use the custom number format "mmmm" to convert each date into a text string equal to the month name. The format "mmmm" tells the TEXT function to extract the full month name from a given date.

Older versions of Excel

In older versions of Excel, there is no SEQUENCE function. This means we don't have an easy way to calculate and return 12 dates all at once. However, you can still generate a series of dates by month with a more manual approach like this:

Here, the start date is hardcoded into cell B5. The formula in cell B6, copied down, is:

=EDATE(B5,1)

This formula uses the EDATE function to add one month to date in cell B5. As the formula is copied down, it returns a date one month after the date in the previous row.