How to Convert Month Name to Number in Excel?

Are you looking for a straightforward technique to transform month names into numerical representations within Excel?

If so, you’ve landed in the right place. Excel has options to format a cell as ‘Short Date’ and ‘Long Date’, but unfortunately lacks a direct selection specifically for months.

Therefore, working with ‘months’ in Excel requires the use of smart and effective techniques.

There are multiple ways to get this done, irrespective of your data size. In this article, you will learn three easy methods to convert month names into numbers in Excel. 

The Top 3 Methods To Quickly Convert a Month Name to a Number

For the purpose of this article, we will use a specific dataset (shown below) to demonstrate the application of all the methods outlined for converting “month names” to “numbers”.

The dataset we're going to use as reference in this article

Method 1:  Using MONTH Function

The MONTH function application is the easiest thing you will learn today. This function is ideal for data with the months represented as text. When the cells are not formatted as ‘date’, then the MONTH syntax needs to be partially tweaked to obtain perfect number representations. 

The MONTH function Syntax: 

MONTH (serial_number) 

Let’s check out the Step-by-Step Guide on how to use the MONTH function below:

Step 1– In place of “serial_number”, select the cell with the month name. For example, we will select A2, where January is present for our dataset. 

Along with the cell number, we will add “&1”, and our formula will look like this: 

=MONTH(A2&1)

We need to use this concatenation process in Excel to represent the normal text ‘January’ as a month.

Applying the first formula which is called the month formula to convert month name into number

Step 2– Press Enter, and the converted number will appear in cell B2 as ‘1’, representing the first month of a year. 

The outcome that can be seen January is reflected as 1

Step 3– To apply the formula to all cells, drag the ‘+’ button in the B2 cell or copy (CTRL+C) and paste (CTRL+V) the formula to the remaining cells.

Copy and paste the formula to the rest of the columns

Note– For the “A2&1” section in the above MONTH formula used, you can select it and press the ‘F9’ key to check how this syntax region gets interpreted.   

Also read: Get the Day Name from the Date in Excel

Method 2- Combining MONTH and DATEVALUE Function

Below is the dataset we will use to implement all the methods for “month name” to “number” conversion

Combining the month and date value function as the second method

Step 1– Input the formula

=MONTH(DATEVALUE(A2&1)) 

in cell B2. This action will give the month number for ‘January’ in cell B2 of our dataset.

Input the formula in the data set

Step 2– Press the Enter key to get the Month number for ‘January’ in cell B2 for our dataset. 

The final output of the method 2

Step 3– Using the ‘+’ button in the B2 cell corner, drag it down to obtain a similar result for all the cells or copy (CTRL+C) and paste (CTRL+V) the formula from B2 to the remaining cell

Copy the formula in the remaining columns

A serial number is given to the MONTH function by combining the DATEVALUE function. The DATEVALUE function converts a Date (present as a text) to a serial number. This serial number represents the specific date or month in Excel. Essentially, the DATEVALUE function facilitates the conversion of all month names to numbers.

“&1” has the same use case mentioned above in the “MONTH” formula. If you use only the DATEVALUE section of the below formula, a unique serial number will be generated

Method 3: Using TEXT Function

The syntax for the TEXT function will be: 

=TEXT(value, format_text) 

Here, “value” refers to the cell name you need to convert to the respective month number. In the “format_text” section, you need to specify the result format. 

Thus, the formula for our dataset will be: 

=TEXT((A2&1), “mm”)

OR

=TEXT((A2&1), “m”)

Here, “mm” represents the format of the month’s number representation; we will get the result as ‘1’ for January if we just use ‘m’. Or we can use “mm”, you can see the result displayed as “01”.  

Implementation Steps:

Step 1– First, we need to format the B2 cell in a date and number format. Right-click and select ‘Format Cells’ (Screenshot #1). OR Press “Ctrl+1”, and a “Format cells” window will appear.

How to open the format cells option
The dialog box of format cells

Step 2– Select the “custom” option. Then write “mmmm” within the “Type” section. Click on the “OK” button to save this change. This represents that the cell is being formatted in the month format. 

The dialog box of format cells

Step 3– In cell B2, input the formula- =TEXT((A2&1),“mm”). Here “&1” has the same purpose as mentioned in the above methods. 

In putting the formula

Step 4– Press the Enter key for the final output of the converted number for “January” in cell B2. 

The final output as can be

Step 5– Using the ‘+’ button in the B2 cell corner, drag it down to obtain a similar result for all the cells OR copy (CTRL+C) and paste (CTRL+V)  the formula from B2 to the remaining cells.

Copy the formula in the remaining cells

Note– In the formula used above, if you add “mmm” just instead of “mm”, you will have the shorter abbreviation of the month displayed in cell B2. For example, the Text formula with “mmm” used will output answers as ‘Jan’, ‘Feb’ and ‘Mar’. 

Bonus Hack (Applicable in Short forms of Months)

You will sometimes come across months written with a period in the end. Example- “Jan.” when dealing with large chunks of month and date sheets in Excel,

Dealing with these monthly texts can be puzzling. Hence, here is an additional method to convert such Month names into numbers. 

The aim is to make Excel process the columns without the period at the end. We have to use the LEFT and LEN functions together. 

The formula to apply to such a dataset will be: 

=MONTH(LEFT(A2,LEN(A2)-1)&1)
Inputing the formula

By using the “-1” at the end of LEN function we instruct to only take three characters and omit the last character while running the MONTH formula. Both LEN and LEFT function is used to return a number of characters in a given text string. 

Learning this trick will help you manage complex situations with month-based datasets. You’ll notice that B2 displays the same ‘1’ as other methods:

When it comes to managing spreadsheets, there are many ways to convert month names into their corresponding numeric values.

Each formula has its own unique characteristics and mechanisms that make it suitable for different types of data manipulation and analysis tasks. Hence, carefully consider each formula outlined in this guide and apply them accordingly. 

By practicing these formulas, you’ll learn a unique set of skills that can significantly improve your efficiency and accuracy when processing and analyzing data in Excel. With practice, you’ll be able to handle month-name-to-number conversion tasks with ease and convenience.

Other articles you may also like:

Leave a Comment