How to Convert Month Name to Number in Excel (Formula)

Last Updated: November 21, 2023
puneet-gogia-excel-champs

- Written by Puneet

To convert a month’s name into the month’s number, you need to create a date with that month’s name using a DATEVALUE function and then use the MONTH function to get the number of the month. You can see in the following example the formula that we have used.

Formula to Get the Month Number from the Month Name

You can use the following steps:

  1. First, enter the DATEVALUE function in a cell.
  2. After that, you need to enter “01”.
  3. Next, use an ampersand and refer to the cell where you have the month’s name.
  4. Now, again use an ampersand and enter “1900”.
  5. In the end, wrap the MONTH function around the DATEVALUE and hit enter to get the result.
convert-month-name-to-number

To understand this formula, you can break it down into two parts.

In the first part, you have the DATEVALUE, which helps you to create a valid date using the month name. You have 01, January, and 1900 and DATEVALUE creates the date 01-Jan-1900.

2 first part has datevalue

In the second part, the MONTH function returns the month number from the date returned by the DATEVALUE.

second-part-has-month-function

The benefit of using this formula is that if you have the month in the full form, like “January” instead of “Jan”. This formula will still work.

benifits-of-month-and-datevalue

Get the Excel File