Table of Contents
In Excel, there is a built-in function called “MONTH” which allows users to convert a month name to its corresponding number value. This function takes a date as its input and returns the month number of that date. For example, the month name “January” would be converted to the number “1”. This feature is useful for organizing and analyzing data that includes dates and months. It can be easily accessed in the formula bar or by typing “=MONTH” followed by the cell reference of the date.
Excel: Convert Month Name to Number
You can use the following two formulas to convert a month name to a number in Excel:
Formula 1: Convert Month Name to Number Without Leading Zeros
=MONTH(DATEVALUE(A2&1))
This particular formula converts the month name in cell A2 to a month number without leading zeros.
For example, if cell A2 contains August then this formula will return 8, since August is the eighth month of the year.
Formula 2: Convert Month Name to Number With Leading Zeros
=TEXT(A2&1, "mm")
This particular formula converts the month name in cell A2 to a month number with leading zeros.
For example, if cell A2 contains August then this formula will return 08.
The following examples show how to use each formula in practice.
Example 1: Convert Month Name to Number Without Leading Zeros in Excel
Suppose we have the following column of month names in Excel:
We can type the following formula into cell B2 to convert the month name in cell A2 to a month number without leading zeros:
=MONTH(DATEVALUE(A2&1))
We can then drag and fill this formula down to each remaining cell in column B:
Note: You can find the complete documentation for the DATEVALUE function in Excel .
Example 2: Convert Month Name to Number With Leading Zeros in Excel
Once again suppose we have the following column of month names in Excel:
We can type the following formula into cell B2 to convert the month name in cell A2 to a month number with leading zeros:
=TEXT(A2&1, "mm")
We can then drag and fill this formula down to each remaining cell in column B:
Column B now displays the month number (with leading zeros) for each month name in column A.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: