Excel Month List

In this post, we’ll create a list of months with a single Excel formula. To make it more interesting, we’ll allow the user to enter the desired number of months to display and create additional formulas to show the last day of each month, the number of days in the each month, the month number, the year, and a formatted monthly label. Let’s get started.

Objective – Excel List of Months

Before we get too far, let’s confirm our goal.

We would like the user to enter the year and the desired number of months, like this:

Excel input values for year and number of months in the list

And we would like Excel to update our list of months accordingly:

Excel month list complete, includes start date, end date, number of days, month number, year number, and a friendly date label

When the user changes the year or the number of months to be displayed, we want Excel to update it automatically.

Note: not all versions of Excel contain all functions presented below.

Video – Excel Month List Formulas

If you prefer to watch a video for the steps on how to create a month list in Excel:

Written Narrative – Excel Month List

In this written narrative, we’ll create one formula for each column in our Excel month list. Let’s just take them one at a time.

To write our formulas, we’ll need to note the location of the Year and Months input cells (C6 and C7):

Year and Months Excel input cells

Let’s start by writing a formula to populate the From column.

From Column

To populate the From column, we’ll use the DATE and SEQUENCE functions.

The DATE function creates a date value for a given year, month, and day. For example, DATE(2030,1,1) would create a date that corresponds to January 1, 2030.

In our worksheet, we want to retrieve the year value entered by the user in C6, so we could use this formula:

=DATE(C6,1,1)

When we hit Enter, we get a single date value.

But, what we’d really like is for Excel to create multiple date values. Specifically, it should create the number of dates specified by the user in C7. For this, we can use the SEQUENCE function.

In summary, the SEQUENCE function creates a sequence of values, such as 1, 2, 3, 4, 5 and so on. We can use the SEQUENCE function as the month argument of the DATE function, like this:

=DATE(C6, SEQUENCE(C7), 1)

We hit Enter, and bam:

Excel month list formula using the DATE function along with SEQUENCE

Note: if you see the date serial values instead of formatted dates, just apply a date format to the column.

To Column

We can use the same DATE and SEQUENCE functions to generate the To column, which needs to display the last day of the month.

Let’s start with the DATE function again.

There’s a cool trick to know about the DATE function … you can use 0 for the day to return the last day of the prior month. For example, DATE(2030,1,1) would return January 1, 2030 whereas DATE(2030,1,0) would return the day before that, or December 31, 2029.

We can use that to our advantage here to compute the last day of each month. The only twist is that we will need our SEQUENCE function to start at 2 instead of the default of 1. We can define this start value in the third argument of the SEQUENCE function.

So, our formula in C12 would be this:

=DATE(C6, SEQUENCE(C7,,2), 0)

We hit Enter, and bam:

Includes the last day of each month

Days Column

In this case, a fairly easy way to compute the number of days in the month is by looking at the day number of the last day of the month in column C.

The DAY function returns the day number of any date value. So, we can use the following formula in D12:

=DAY(C12)

However, when we hit Enter, the formula does not spill down the range like the first two formulas. No worries, we can append the spill reference operator (#) to our cell reference C12 like this:

=DAY(C12#)

Now we hit Enter, and bam:

Includes the number of days in each month

Month Column

To compute the Month column, we can use the MONTH function which returns the month number of any date value. We can reference the date value in either column B or C as desired. We’ll need to be sure to use the spill reference operator so that Excel fills the formula down, like this:

=MONTH(B12#)

We hit Enter, and bam:

Includes the month number of each month

Year Column

The Year column can be computed using the YEAR function, like this:

=YEAR(B12#)

And Bam:

Includes the year number of each month

Label Column

For the Label column, we can use the TEXT function to display a date value in our desired format. For example, the format code “mmm” would display the three-letter month abbreviation. The format code “mmmm” would display the month name fully spelled out. The format code “mmm yyyy” would display the three-letter month abbreviation along with a four digit year. The many format code options are documented in the Excel help system in case you’d like to explore others.

In our case, we’ll use the following formula in G12:

=TEXT(B12#,"mmm-yyyy")

We hit Enter and bam:

Includes a friendly label according to the designed format code

Yay … we did it!

And, if the user enters a different year or number of months, Excel instantly updates the worksheet accordingly.

Conclusion

If you have any questions about the functions used, please let me know in the comments section. Also, if you have any alternatives or suggestions to improve the formulas, please share by posting a comment below … thanks!

Sample file

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

6 Comments

  1. John on November 3, 2021 at 9:14 am

    I’m glad you’re here. Let’s just jump right in. By the way I like the new beard.

  2. Andrew Randall on November 3, 2021 at 10:08 am

    Very useful, Jeff. I would like to enhance the functionality by having the start month specified, i.e. a number of months starting from, say, April. Be useful for year-ends that don;t match the calendar – like most of them!

  3. Jessica on November 3, 2021 at 7:55 pm

    Thanks John, Great tips every time.
    Just one question, for the “To” column, why can’t I use EOMONTH(B12#,0) instead?

  4. Daniel Dion on November 6, 2021 at 5:18 pm

    Here’s another approach with just one formula
    =LET(start, DATE(2021,SEQUENCE(12),1),
    end, EOMONTH(start,0),
    days, DAY(end),
    month, MONTH(end),
    year, YEAR(end),
    label, TEXT(end,”mmm-yyyy”),
    result, CHOOSE(SEQUENCE(,6),start,end,days,month,year,label),
    result)

Leave a Comment