Explanation
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
The 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 the date in cell B5. As the formula is copied down, it returns a date one month after the date in the previous row.
Explanation
The SEQUENCE function is a dynamic array function that can generate multiple results. When used by itself on the worksheet, SEQUENCE outputs an array of results that " spill " onto the worksheet in a " spill range “.
In the example shown, we want to generate 12 times, one hour apart, starting at 7:00 AM (the value in B5). To do this, we use the TIME function, which can create a valid Excel time with hours, minutes, and seconds given as decimal values.
To create the numbers used for hours with SEQUENCE, we have:
SEQUENCE(12,1,HOUR(B5))
The HOUR function is used to convert the time in B5 to a decimal value for hours (7). So the function resolves to:
SEQUENCE(12,1,7)
which generates an array of numbers like this:
{7;8;9;10;11;12;13;14;15;16;17;18}
This array is returned to the TIME function as the hour argument:
=TIME({7;8;9;10;11;12;13;14;15;16;17;18},1),0,0)
The TIME function returns 12 times to a spill range beginning in cell D5.
With raw numbers
The example above used the TIME function for convenience, but it is also possible to work with numeric values directly. Since Excel time is recorded as fractions of a day , the formula above can be written like this:
=SEQUENCE(12,1,B5,1/24)
Dynamic Array Formulas are available in Office 365 only.
