We can use the MONTH and DATEVALUE combination to convert a month name to the corresponding month number in Google Sheets.

Using the formula is quite simple, but you might also be interested in understanding how date functions can convert text to a number.

To convert a month name to a month number, you just need to concatenate the text with any number in the range 1 to 28, ideally concatenating the number 1. Then, use that within the DATEVALUE and MONTH combination.

Why can’t we use (concatenate) any other number, or are there any restrictions? All of these aspects will be explored in this tutorial.

Note: You might have seen the use of the MONTH function alone, but that is not a perfect approach (we will see that in the formula part below).

Formula to Convert Month Name to Number in Google Sheets

Assuming cell A1 contains the month name “March” or the abbreviated name “Mar,” you can convert this to 3 using the following formula:

=IFERROR(MONTH(DATEVALUE(A1&1)))

In this formula, the “&” sign is used to concatenate the number 1 to the text in cell A1. Alternatively, you can use the CONCAT function as well.

If you use CONCAT instead of the ampersand, the formula will become:

=IFERROR(MONTH(DATEVALUE(CONCAT(A1, 1))))

Replace “March” with any month name in cell A1, and the formula will return the corresponding month number.

Formula Explanation

Let’s learn the formula in detail: The logic of concatenating the number 1 and the roles of each function.

MONTH Function and the Logic in Converting Month Name to Number

The MONTH function in Google Sheets is a date function designed to return a month number from a given date, with the following syntax:

=MONTH(date)

It takes a date argument. But how does it convert a month’s name to a number?

To understand the logic, enter “Mar” or “March” or any month name in full or abbreviated form, followed by the number 1. For example, enter “March1” in cell B1. Check the underlying value of the entered cell in the formula bar.

If the current year is 2024, you will see the date 01/03/2024 (or 03/01/2024, depending on your default date formatting in Sheets).

The DATE function has no issue extracting the month number from a date.

When you concatenate the month string with a number, the MONTH function reads it as a date and returns the corresponding month number.

Then why do we use the DATEVALUE function with it?

Role of DATEVALUE in Converting Month Name to Number

When using the formula =IFERROR(MONTH(DATEVALUE(A1&1))), assume cell A1 is blank. A1&1 will return 1.

The number 1 is equal to the date 31/12/1899 in Google Sheets. So, the MONTH formula, =MONTH(A1&1), will return 12 as the month number. The DATEVALUE prevents that. How?

The DATEVALUE function converts a date string to a date value, and all other values to errors. So when A1&1 returns 1, DATEVALUE returns an error. Therefore, the ‘outer’ MONTH function can’t return any month number. The IFERROR removes the error.

So, the correct approach to convert a month name to a number is using the above DATEVALUE and MONTH combo.

Why Opt for the Number 1?

As mentioned earlier, you can use any number from 1 to 28 to concatenate with the month string. Google Sheets interprets that number as the day. The safer range is 1 to 28. Using any other number may cause issues in certain months, such as February, which has 28 or 29 days.

How to Convert Month Names in a Column to Corresponding Numbers in Google Sheets

You can convert month names in an array into corresponding month numbers.

Specify the range containing the text strings and wrap the formula with the ARRAYFORMULA function. No other changes are required since we have already taken precautions to handle blank cells in the range.

To convert the month names in column B, range B2:B, to corresponding month numbers, use the following formula in cell C2:

=ArrayFormula(IFERROR(MONTH(DATEVALUE(CONCAT(B2:B, 1)))))

Resources

The above is the error-free approach to converting month names to numbers in Google Sheets. Here are a few more tutorials related to month names.

1. Google Sheets Query: How to Convert Month in Number to Month Name in Text
2. Convert Month Numbers to Month Names in Google Sheets
3. Formula to Sort By Month Name in Google Sheets
4. Month Name as the Criterion in Date Column in Query Function in Google Sheets