Convert Month Name to Month Number in Google Sheets

Published on

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)))
Conversion of Month Name to Number in Google Sheets (Correct Method)

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.

What Happens When Adding 1 to a Month Name

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)))))
Array Formula for Converting Month Names to Month Numbers

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
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

2 COMMENTS

  1. Thanks for this tip that worked fantastically in all circumstances but one. If the month is formatted as Mmm (ie Jan, Feb, Mar) the formula returns the correct month date apart from “Sep” which returns a value error. “Sept” works as does “September”, but not “Sep”.

    Any idea why this might be?

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.