How to Convert Month Name to Number in Excel?

by

Converting a date to month name is quite common, but sometimes there may be a need to convert the month name to its corresponding month number.

For example, you might have the text January or February in a cell and you want to convert it to the month numbers (which would be 1 for January or 2 for February)

You might need to do this to simply fulfill a formatting requirement or maybe you need to perform some calculations with it.

Whatever the reason, we are going to show you how to convert a month name to a number in Excel.

In this tutorial we will cover two situations:

  • When an actual DATE is formatted to display the month name
  • When the month name is a simple TEXT string

In the first case, we will demonstrate two different ways to convert the month name to text:

  • Using Excel’s Format cells dialog box
  • Using the TEXT function.

In the second case, we will use a formula involving the DATEVALUE and MONTH function to convert the month name string to its corresponding month number.

How to Convert Month Name to Number in Excel

Before converting the month name to a number, you need to check if the month name is stored as a DATE or a TEXT value.

You can decide your next step depending on the actual data type of the month name.

Let us see how you can tell what the underlying format is and how you can perform the conversions in each case.

Converting Month Name to Number when the Month Name is a Formatted DATE Value

In Excel, it is possible to represent a date in the “mmmm” or “mmm” format.

These formats represent the underlying date with the full or partial month name respectively.

For example, the date 01/01/2021 is displayed as “January” if it is specified in the “mmmm” format, and as “Jan” if specified in the “mmm” format.

To check if your month name is actually a DATE value, simply click on the cell.

The formula bar will show what the underlying or actual value of the cell is.

If you see a date in the formula bar, it means that the cell contains a date, which has been formatted to display the month name corresponding to that date, as shown below:

Check the month name is in the date format

In such cases, all you need to do is simply change the format of the cell containing the date. You two have options to go about it.

  • If you want to make the change on the original cells, then you can use the Format Cells option. 
  • If you want to display the changed date in a separate cell, you can use the TEXT function.

Let us see how to go about both ways. We will use both methods to convert the following list of dates (displayed as month names) to month numbers:

Month name data

Converting Month Name to Number Format using the Format Cells Dialog Box

Here are the steps that you need to follow if you want to change the format of a cell from month name to month number using the Format Cells dialog box:

  1. Select the cells containing the month names that you want to convert
  2. Press CTRL+1 from your keyboard or right-click and select “Format Cells” from the context menu that appears. This will open the Format Cells dialog box.
  3. Click on the Number tab and select “Custom” from the list under Category.
Specify the custom format for teh month
  1.  In the field under “Type”, you should see the current format of the cell, which is most probably “mmmm” or “mmm”. Change this to either “m” (if you want to display your month number without a leading 0) or “mm” (if you want to display the month name with a leading 0 for single digits, eg: 01).
Enter m to get the month number
  1. Click OK to close the dialog box.

Your cells should now display the month numbers instead of names.

Month names have been converted to month numbers

Note: This method makes changes to the format of the original cells. If you want to retain the original month names and want to display the converted results in separate cells, then you should use the next method.

Converting Month Name to Number Format using the TEXT Function

To display the month number in a separate cell, you can use the TEXT function. This function converts numeric values (including date and time values) to text according to a specified format. The syntax for this function is:

TEXT(number, format)

Here,

  • number is the number, date or time that you want to format
  • format is a string specifying how you want the number to be formatted.

Note that the format parameter should always be enclosed in double-quotes.

Since we want to format the date to display its corresponding month number, we can specify the string “m” or “mm” as the format string.

Use “m” if you want to display the month number without a leading 0 and “mm” if you want to display it with a leading 0 for single digits.

So, to display the month number corresponding to the date in cell A2, you can incorporate the appropriate format string into the TEXT function as follows:

=TEXT(A2, “mm”)

Or

=TEXT(A2,”m”)
TEXT formula to get the month number from month name

Note: Notice that the numbers are all displayed aligned to the left side of their cells. This is because these numbers are all in TEXT format, which means you cannot perform calculations with these numbers. 

If you want to use your resultant month numbers in calculations, you will need to first convert them to numeric values. For this, copy the values and then “Paste as Value”:

Paste as values

You may see a small green triangle in the upper-left corner of the resultant cells, as shown below:

Green triangle at the top left of the cell

This error indicator means that the number is still stored as text.

To get rid of this error, select all the cells containing the month numbers and click the error button that appears near the cells:

Click the yellow error icon

This will display a dropdown list of actions to perform in relation to the error. Select the “Convert to Number’ option:

Click on Convert to Number

Your cells should now contain the month numbers as pure numbers, instead of text. You can confirm this by looking at how the numbers are aligned. If they are aligned to the right, it means the cells now contain numeric values instead of text.

Month numbers that are stored as numbers

Converting Month Name to Number when the Month Name is a Text Value

If your month name is stored as a text string, then you will need to use a different approach to convert them to month numbers.

You can tell if the value in the cell is a text value by looking at how it is aligned in the cell. If the value is aligned to the left, it is most likely to be a text value, as shown in the list of month names below:

Month name stored as text

In such cases, you can use a combination of the MONTH and DATEVALUE functions to obtain the month number corresponding to the given month name.

The DATEVALUE function takes a date value in any valid format and returns the same date in the form of its corresponding serial number.

You might be aware that Excel internally represents every date as a serial number starting from January 1, 1900.  The serial number increments by 1 for every day since that date. For example, it represents the date 01/01/2021 as 44197, since this date is 44197 days away from 01/01/1900.

Now the only problem is that we have the month name, which is not a complete date, so the DATEVALUE will not recognize it as a date. 

To ensure that the function sees a proper date, we can append a number to the end of the month name string. So if we use the string “January1” as a parameter, the DATEVALUE function sees it as a proper date, i.e. January 1st of the current year, and it returns the corresponding serial number 44197, which is nothing but a date!

Once we get the date, we can use the MONTH function to extract the month number from it.

So, to display the month number corresponding to the month name in cell A2, you use the following formula:

=MONTH(DATEVALUE(A2&”1”))

Copy this formula to the rest of the cells in the column, and here’s what you get:

MONTH and DATEVALUE formula to convert month name to number

In this tutorial, we saw how to convert month name to number in two cases – when the month is a TEXT value and when it is actually a DATE formatted to display only the month name.

In the first case, you can use a formula, while in the second case, you can simply change the cell’s formatting or use the TEXT function to display the date as a month number rather than the month name.

I hope the tutorial was useful and easy to follow.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment