This article shows you how to generate month list by formulas and how to display Excel month names instead of actual date values. We will use DATE, YEAR and MONTH formulas as well as Number Formatting.

Syntax

=DATE( YEAR( previous date ), MONTH( previous date )+1, 1)

Steps

1. Start list with a static date 1/1/2019
2. Press Enter key to continue on the cell below
3. Type =DATE(
4. Continue with YEAR(B3), formula, be sure that the argument is the cell above
5. Next is MONTH(B3)+1, formula to increase previous date's month by 1
6. Type 1
7. Type ) to close the DATE function and finish the formula
8. Copy and paste down the formula
9. Apply desired number formatting on dates

How

To generate dates we will use the DATE function. The DATE function returns a date value by its year, month and day arguments. Because we want to continue from a previous date, we get the previous date's year and month values to use in the DATE function.

Excel has built in formulas to get year and month values. They are YEAR and MONTH respectively. They return the values as their name refer. To increase month by 1, we add 1 to return the value of the MONTH function. Because the day of month is not a concern for this type of list, the first day of a month, 1 becomes a proper day value.

=DATE(YEAR(B3),MONTH(B3)+1,1)

Excel keeps date and time values as numbers. This behavior allows us to apply a number format on the date values as well. Actually, either the month/day/year or day/month/year notations are a number format as well.

To apply a number format to a cell;

1. Select the cell (or range)
2. Press Ctrl +1 or right-click and select Format Cells
3. Select a built-in formats or select Custom and enter yours

In this example, we will apply "mmm 'yy" and "mmmm yyyy" number formats which converts the date values into Excel month names like Jan '19 and January 2019 respectively. For more information and examples on custom number formatting, please refer to the article: Number Formatting in Excel – All You Need to Know