Converting Month Number into Month Name in ExcelTwo Simple to Follow Methods

Written By:
Adiste Mae
Reviewed By:
FundsNet Staff

When working with Microsoft Excel, there are instances where you need to use dates.

A lot of projects rely on the date data to organize their inventory, sales, attendance, etc.

Sometimes, these dates are in number format and you are sometimes required to convert them into word format depending on the given date.

Here, we are discussing the two different ways to convert the month number to the month name in Excel:

  • By the Function TEXT 
  • By the Function CHOOSE

Moreover, we will also tackle steps and formulas on how to convert a given date into its month name.

  • By the Function TEXT 
  • By the Feature Format Cells 
  • By the Function CHOOSE

Month Number to Month Name Conversion in Excel

There are cases when you need to consider the conversion of month numbers to month names.

In Excel, there are two ways to achieve this by using these functions:

  • TEXT function
  • CHOOSE function

Let’s look at how we can use these functions.

Month Number to Month Name Conversion (By Using TEXT Function)

In using numbers to denote their corresponding dates (not in date format), Microsoft Excel does not recognize these numbers as a date.

For our example, 1, 2, and 3 correspond to January, February, and March so on and so forth.

So for Excel to recognize these numbers we need to convert these numbers to the format that is needed. The TEXT function can be used to help you with this problem.

For example, we are given the number 6 in cell A2 and you are required to convert it into its corresponding month name. Here is the formula for the TEXT function:

=TEXT(A2, “mmmm”)

However, this method is not that simple because Excel does not consider a month’s name.

Excel only recognizes this as the day in a month. So the number 6 given in cell A2 is considered as day 6 in a month. So the number 1 to 31 is considered as a day of a month.

Thus, if we want to get the month that corresponds to the number given in the cell, we need to multiply the number by 29 to get the month.

For our illustration, we need to multiply the given number in cell A2 which is number 6 to 29.

You will get a product of 174, which is 174 days. If you convert it to month, the result will be in the month of June.

You can see the illustration using different numbers in every cell:

Note:

If you need to use the shortcut version of the month (Jan, Feb, Mar, etc.), just simply remove one “m” in the formula above. Our formula now is: =TEXT(A2, “mmm”).

Month Number to Month Name Conversion (By CHOOSE Function)

There are other ways to convert numbers to month names by using the CHOOSE function.

The function returns a value from a list through a given index. The formula is given below:

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

Where:

  • index_num – the position you want to choose. It is an integer value between 1 and 254
  • value 1 – the value of the first index
  • value 2, value 3, etc. – data values of the subsequent indices. Value 2,3 and so on is the second & third index and so on.

This method follows a sequence, so it is easy to assign month names to numbers. The CHOOSE function can input the month names you want to return as values.

Let’s say we have a given number in the A2 cell, here is the formula that we are using:

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

This function will use the month number located in cell A2.

Then it will use the given number corresponding to its month name in the given formula.

Here is the illustration of how the CHOOSE function works:

This function is more reliable than the TEXT function mentioned above because you can program the month number to several sets of values that you need.

This function is also customizable. You can personally choose the month values (month names) whether it is in any form, abbreviation, non-abbreviation, or any kind of language you want.

You can see in the picture below how you can use the CHOOSE function in any way you want:

One advantage of this method is the easy year customization, whether it is for a calendar or a financial year.

When you like the 1st month of the business year to start with July and represent it as 1, you may do so and make the necessary changes to the formula. 

Converting a Given Date to a Month Name

Let’s say you are given a date and you need to convert it into a month name.

The MONTH Function will be inserted and will replace the cell reference in the formulas above.

Date to Month Name Conversion (By TEXT Function)

In a certain cell (A2), you are given a date of 12/24/2022.

Using the Text function, you can extract the month name from the date. Here is the formula:

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

You can see that it displaces the full month’s name in the given date below.

Note:

If you need to use the shortcut version of the month (Jan, Feb, Mar, etc.), just simply remove one “m” in the formula above.

Our formula now is: =TEXT(A2, “mmm”).

Date to Month Name Conversion (By CHOOSE Function)

In a certain cell (A2), you are given a date. By using the CHOOSE function, you can extract the month name from the date. Here is the formula:        

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

Note:

If you have the MONTH function with the cell to the given date as the first parameter, you can use the CHOOSE function to display the month in any custom format that you like.

Date to Month Name Conversion (By Format Cells Feature)

There is another method for converting a date to a month name.

This method is called the Format Cell feature. Instead of using another cell with a built-in function, you can use this method in converting a date to a month name fast and easily.

Here is the step-by-step process:

  1. Select the cells that you want to convert. Then, right-click and select ‘Format cells.
  2. The ‘Format cells’ dialogue box will now be open. Select the ‘Number’ tab.
  3. Now, select the ‘Custom’ option from the Category list.
  4. Under ‘Type’, just type “mmmm”. And if you want the abbreviation of the month, just type ‘mmm’.
  5. Click the OK button.
  6. Now the month name will appear where you selected your dates to appear.

Conclusion

In this short tutorial, you have learned about the two ways to convert a number to a month name.

You have also learned how to extract the month names from a given date.

Hope this helps you a lot with your project.