Exceljet

Quick, clean, and to the point

Sequence of months

Excel formula: Sequence of months
Generic formula 
=EDATE(A1,SEQUENCE(12,1,0))
Summary 

To generate a series of dates incremented by month, you can use the SEQUENCE function together with the EDATE function or EOMONTH function. In the example shown, the formula in D5 is:

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

which outputs a series of 12 dates, incremented by one month, beginning on May 1, 2019.

Explanation 

The EDATE function moves forward or backwards in time in one-month increments from a given start date. The SEQUENCE function is a dynamic array function that can generate multiple results that "spill" onto the worksheet in a "spill range".

SEQUENCE can generate results in rows, columns, or both. In this case, SEQUENCE is configured to output an array of numbers that is 12 rows by 1 column:

SEQUENCE(12,1,0)

The start is zero, and step value defaults to 1, so SEQUENCE outputs an array like this:

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

This array is returned to as the months argument inside the EDATE function. EDATE then returns 12 dates, beginning with the date in B5. Excel dates are stored as serial numbers, so the output looks like this:

{43586;43617;43647;43678;43709;43739;43770;43800;43831;43862;43891;43922}

When these results are formatted as dates, the result is 12 dates incremented by one month, beginning May 1, 2019.

End of month

To generate a series of "end of month" dates, you use the EOMONTH function instead of EDATE. The formula in F5 is:

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

Month names only

To generate a list of twelve month names starting from the current month you can wrap the formulas above in the TEXT function:

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

Alter the start date to start month names in a different month. For example, to start names in January, use the DATE function to supply an arbitrary January date:

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

Any January date will achieve the same result, since EDATE will create dates in the next 12 months, and TEXT will convert these dates into month names. The TEXT function uses custom number formats to convert numbers into text. Read more on custom number formats here.

Dynamic Array Formulas are available in Excel 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.