Knowing how to convert a month name to a number in Excel enables effortless transformation of month names to numerical values, making the data compatible with formulas.
Frequently, raw datasetsβsuch as those tracking sales, employee performance, revenue, or visitor countsβcome with month names instead of numbers. To work effectively with formulas, youβll need to convert these text-based month names into numerical equivalents.
In this Microsoft Excel tutorial, Iβll guide you through several methods to convert month names into their corresponding numbers. Letβs dive in!
Using a Custom Number Format
You can use the Format Cells dialog box along with a custom number formatting code to transform months to their corresponding numeric values. This method is applicable to a date data format in Excel that only shows the month name while the underlying data is a short date, like 1/3/1977
.

When a month name in an Excel cell is in the right alignment it means a custom formatting code has been applied to visualize the month name in such a way.

For instance, apply the mmmm
code to a short date to show the corresponding month name.

Now, if you’ve got any such datasets, go to the source worksheet and select the entire column containing only the month names.
Now, press Ctrl + 1 to bring up the Format Cells dialog box.
Click on the Custom option below the Category column.
Enter mm
for month numbers in two digits or m
for month numbers in one digit in the Type field on the right side.
Click OK to apply the custom number formatting.

You should now see the numerical values of the months, like 01
or 1
for January
, 02
or 2
for February
, and so on.
π Read More: 4 Ways To Convert Month Number To Name in Microsoft Excel
Using the TEXT Function
Another way to convert a month name to its corresponding numeric representation is by using the Excel TEXT function. It’ll only work on a dataset containing month entries in standard Excel date format. For example, Wednesday, January 1, 2024
is a long date code. It’s suitable for the TEXT function to extract the month number.

Select the cell where you want to get the output and enter the following formula:
=TEXT(A2,"m")
In the above formula, A2
is the source of the month name from a long date format value. You can change this cell reference according to your own data.

Hit Enter to calculate the cell.

Click on the fill handle and drag it down to replicate the formula in the rest of the cells.

There you go! You’ve got the number equivalents of the month names.
Using the SWITCH Function
The SWITCH function in Excel lets you match a value (like a word or number) against a list of options and return a specific result for each match. Itβs like asking a series of if this, then that questions but in a much simpler way. For example, you can use it to turn January
into 1
, February
into 2
, and so on, all in one formula!
You can use this function directly on month names expressed as text strings in an Excel worksheet. Keep in mind that the function is only available in Excel 2019 and later editions.

To try it out, select the destination cell where you want the numeric values equivalent to the month’s name.
Type in the following formula:
=SWITCH(A2, "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12)
In the above formula, you need to change the source cell, which is A2
.

Now, hit Enter to convert the month name in A2
into its numeric value, which is 1
.

Use the fill handle to copy and paste the formula into the rest of the cells in your data column.

You’ve successfully transformed month names into numerical values.
Using VLOOKUP With Named Ranges
You can combine Named Ranges with the VLOOKUP function as well to transform month names into numbers. Defined Names keep your formulas clean and tidy, so youβre not stuck deciphering messy cell references.
If you ever need to update your month mapping, you can do it in one place instead of hunting through your spreadsheet. VLOOKUP then does the heavy lifting, matching month names to their numbers quickly and accurately.
Find below two simple steps to transform month names into numerical values:
Define the Named Range

Create a data table containing the month names and their corresponding numerical values as shown above in the screenshot.

Now, select the entire dataset and click on the Define Name command in the Defined Names block of the Formulas tab.

Enter a text in the Name field you can remember, like MonthName
, and click OK to set up the Named Range.
Formula Using VLOOKUP

Go to the destination cell where you wish to generate the numerical value against a month name.
Type in the following formula into the cell and hit Enter:
=VLOOKUP(TEXT(A2, "mmmm"), MonthNames, 2, FALSE)
Don’t forget to replace A2
with the destination cell reference according to your worksheet.

You can now replicate the same formula in the rest of the cells by dragging down the fill handle.

An upside of this method is you can apply this in any worksheet of the same workbook by simply referring to the Named Range you created once.
Using Power Query Editor

This method is suitable for large datasets imported from external sources. To open the source data in Power Query, go to the Data tab and click on the Get Data command. Hover the mouse over data sources, like From Database, and use a data connector from the overflow menu.

In the current exercise, I’m exporting a dataset from the Excel worksheet I’m working on using the From Table/Range command.
Power Query Editor loads the source dataset once you click OK on the Create Table dialog.

Click on the Custom Column command in the Add Column tab on Power Query.
Type in a text in the New column name field, like Month Numbers.
Enter the following Power Query M code into the Custom column formula field:
Date.Month(Date.FromText("1 " & [MonthName] & " 2000"))
Change the Month Name
with the column header where you’ve got text strings.
Click OK to create the custom column.

You should see a new column showing month numbers relevant to the adjacent month name column on the left side.

Click the File tab and select Close & Load To from the context menu.

Use the Import Data dialog box to select a destination cell on the active worksheet. Click on the Existing worksheet option and highlight the cell.
Remove the old column to reduce clutter.
Congratulations! You’ve learned how to convert month to number in Excel using Power Query.
π Read more: Did you enjoy the guide to learn something new from Excel? You’ll surely like these resources too:
Conclusions
So far, you’ve learned different ways to convert month names to numbers in Excel for values available as short dates, long dates, and text strings.
The Format Cells-based method is suitable where you want to make the adjustment within the source column quickly.
You can also use the formula-based approaches when you see these fit your requirements, like integrating the process into a different formula.
Finally, the Power Query-based approach allows you to convert a large dataset of month names into their corresponding number values without slowing down the workbook performance.
Which method did you find the most useful? Comment below to let me know. If you’ve got any suggestions for me, share those in your reply.
0 Comments