How to Convert Month Number to Month Name in Excel

When working with date-related data, there might be instances where you need to convert a number to its corresponding month. There might also be instances when you might want to extract the month name corresponding to a given date.

In this tutorial we will discuss two ways to convert a month number to a month name in Excel:

Using the TEXT function
Using the CHOOSE function

We will also discuss three ways to convert a given date into the corresponding month name.

Using the TEXT function
Using the Format Cells feature
Using the CHOOSE function

Table of Contents

How to Convert a Given Number to Month Name in Excel

Let us first consider the case where you want to convert a month number to month names. There are two ways to accomplish this in Excel:

using the TEXT function
using the CHOOSE function

Let us look at each of these ways one by one.

Using the TEXT Function to Convert Month Number to Month Name in Excel

When you use just a number to denote a month (not a date or date serial number), Excel usually cannot make sense of it. It does not understand that the number 1 means January, 2 means February, and so on.

So we will need to convert the number to a format that Excel understands. This can be done with the help of the TEXT function.

Let’s say you have the number 5 in cell A2 and you want to convert it to the corresponding month name. You could use the TEXT function as follows:

=TEXT(A2, “mmmm”)

But that would not give you the exact month you are looking for. This is because Excel considers the number 1 as day 1, number 2 as day 2, etc. So any number between 1 and 31 will correspond to a day in the month of January.

Therefore, if you want to find out the month corresponding to a number, you can multiply the number by 29, so that we get the number corresponding to a day in that month.

For example, if you multiply 5 by 29, you get 145, which can be any day in the 5th month of the year. The 5th month of the year is May, so the above function returns the month name, May.

The following screenshot shows how the above formula works with different number inputs:

Note: If you want the shorter version of the month name, like Jan, Feb, etc. then you can use the string parameter “mmm” instead of “mmmm”:

Using the CHOOSE Function to Convert Month Number to Month Name in Excel

The CHOOSE function provides another great way to convert a month number to the month name in Excel.

The Excel CHOOSE function returns a value from a list using a given position or index. The syntax for the CHOOSE function is as follows:

=CHOOSE (index_num, value1, [value2], ...)

Here,

index_num is the index or position of the value you want to choose. It can be an integer value between 1 and 254.
value1 is the value corresponding to the first index.
value2, value3, etc. are the values corresponding to the subsequent indices. So value2 corresponds to the second index, and so on.

This makes it really easy to assign month names to numbers, since they follow a sequence. You can enter the month names you want to return as values in the CHOOSE function, after the first argument. The first argument can specify a month number or a reference to the cell containing the month number.

To convert a month number in cell reference A2, for example, we can use the CHOOSE function as shown below:

=CHOOSE(A2,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

The CHOOSE function will then use the month number specified in cell A2, say n, to extract and return the nth value in the list.

The following screenshot shows how the above formula works with different number inputs:

This method is more flexible than the first method (using the TEXT function) because it allows us to map the month number to any set of values you want.

You can customize the month values (month names) inside the CHOOSE function to any form. They could be abbreviated, not abbreviated, or even in a different language.

You can see from the screenshot below different ways of using the CHOOSE function to customize month names:

One good thing that I like about this method is that you can customize it easily for the calendar year as well as the financial year. For example, if I’m working with financial data and I want 1 to represent April and 2 to represent May (as so on), then I can easily tweak this formula

How to Convert a Given Date to Month Name

Now, what if, instead of a month number, you are given a date, and you need to extract the month name from it?

In that case, all you need to do is to replace the cell reference in both the above methods with the MONTH Function.

Using the TEXT Function to Convert a Date to Month Name in Excel

Let’s say you have the date 04/06/2021 in cell A2. You can then use the TEXT function to extract the month name from the date as follows:

=TEXT(MONTH(A2),”mmmm”)

This will display the full month’s name corresponding to the date.

Note: If you want the shorter version of the month name, like Jan, Feb, etc. then you can use the string parameter “mmm” instead of “mmmm”:

Using the CHOOSE Function to Convert a Date to Month Name in Excel

If you want to convert the date, which is in a cell, say A2, then you can use the CHOOSE function to display the month corresponding to the date, as follows:

=CHOOSE(MONTH(A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Note: As mentioned before, you can use the CHOOSE function to display the month in any custom format that you want, as long as you have the MONTH function with the cell reference to the date as the first parameter.

Using the Format Cells Feature to Convert a Date to Month Name in Excel

A third technique to convert a date to a month name is using the Format Cells feature. This technique is ideal and quick if you want to convert the date in place, instead of having to use a separate cell with a function.

To use this method, follow the steps below:

Select all the cells containing the dates you want to convert.
Right-click your selection and click on ‘Format cells’ from the context menu that appears.
This will open the ‘Format Cells’ dialog box. Select the ‘Number’ tab.
From the Category list on the left side, select the ‘Custom’ option.
In the input box just under ‘Type’ (on the right side of the dialog box), type the format “mmmm” if you want the full month name, or “mmm” if you want the abbreviated version of the month name.
Click OK.
Your selected date values should now be replaced with the corresponding month names.

One great thing about this method is that it is not going to change the value in the cell. It will only change the way that values displayed in the cell. For example, if after applying this custom number formatting method, your date may be shown as April or May (or whatever the month name is), but in the backend, it would still hold the full date and you can use it in calculations.

In this tutorial, I showed you two ways to convert a month number to a month name.

I also showed you how to extract the month names from a given date value.

I hope you found the tutorial helpful.

Other Excel tutorials you may also like: