Knowing how to convert month name to number in Google Sheets is useful if you have your month name written as a text but need it as a number so that the formula you will use can identify it.
Table of Contents
Converting textual month name to month number implies exactly that. If you type the name of the month as a text (for example, ‘January’), the formula will return it as a number (in our example it will be ‘1’).
There is no such function you can use to convert textual month name to month number. However, it can be done by using one of the Google Sheets date functions. There is just one little trick on how to do it and we are going to show it to you.
Let’s take an example to make it even more clear.
Say you have a list of employees, with their names and hire dates but the month is written as a text. Now you would want to sort hire dates to month wise order but you cannot do it since the months are written as a text and the Google Sheet will sort them alphabetically.
We will use the MONTH function to return the value of the month in numeric format.
But, do you remember the different date values we used to explain how the MONTH function in Google Sheets works? When we entered just the name of the month as a text, the MONTH function returned the error (#VALUE!).
So how do we do that?
Simple. We will use the trick we mentioned before. After the cell reference that contains our month name, we will add ‘&1’ (or any other number lower than 30/31 (or 28/29 if our month is February)). So it will automatically convert the month name to a date which the MONTH function in Google Sheets accepts.
Let’s take a look at the anatomy of the MONTH function, to help you understand how to use this little trick in Google Sheets.
The Anatomy of the MONTH Function
The MONTH function is quite simple and its syntax (the way we write it) is as follows:
=MONTH(date)
=the equals sign is the sign we put at the beginning of any function in Google Sheets.MONTH()is our function.dateis the unit of time from which to extract the month.
As mentioned before, now we do not have the actual date. We only have textual month names so our function will look like this:
=MONTH(date&1)
where ‘&1’ can be any number from 1 to 30/31 (or 28/29 for February), depending on how many days the chosen month has.
How to Convert Month Name to Number in Google Sheets
Let’s apply our little trick to the MONTH function in Google Sheets, so you can take a look at how it works and what should you pay attention to.
- To make a cell active, you should first click on it. For this guide, we will use the cell D3.
- Type the equals sign ‘=’ to start of the function. As you start typing the name of the function, which is ‘MONTH’, you will get auto-suggestions of the functions that start with the same letters. You can continue typing or you can choose the
MONTHfunction from the suggestions.
- Enter the opening round bracket ‘(‘ and input the cell reference which contains the name of the month you want to convert to month number. In our example, it is cell B3.
- After the cell reference, you should add ‘&1’. Depending on the number of days each month has, you can add any number from 1 to 30/31 (or 28/29 if the month is February).
- If the number you entered is higher than the number of the days the chosen month has, the MONTH function will return the error (#VALUE!). Type ‘&51’ and take a look.
- Remove ‘&51’ and type ‘&1’. To close the function, type the closing round bracket ‘)’ or hit the Enter key on your keyboard. If you did everything correctly, the formula will look as =MONTH(B3&1) and it will return the month number. In our example, the month is ‘January’ so the formula returned the number ‘1’.
Repeat these steps for other rows and you will get the textual month names converted to month numbers. Just remember to change the cell references accordingly. Now you can use these numbers to sort dates to month wise order.
That’s it! You did it! Now you know how to convert month name to month number in Google Sheets using the MONTH function and the little trick we showed you!
You can give it a try yourself by making a copy of the spreadsheet using the link below:
Also, take a look at the other Google Sheets formulas you can use to sort and filter your data more effectively 🙂 🙂
