Excel: How to 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:

Column B now displays the month number (without leading zeros) for each month name in column A.

Note: You can find the complete documentation for the DATEVALUE function in Excel here.

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.

The following tutorials explain how to perform other common tasks in Excel: