5 Ways to Convert Month to Number in Microsoft Excel

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.

Short dates show only month names
Short dates show only month names

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.

Custom number formatting code
Custom number formatting code

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

Applying a custom number code
Applying a custom number code

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.

Convert Month to Number using Format Cells
Convert Month to Number using Format Cells

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.

TEXT formula
TEXT formula

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.

Calculate cell
Calculate cell

Hit Enter to calculate the cell.

Using the fill handle
Using the fill handle

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

Convert month name to number using TEXT
Convert month name to number using TEXT

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.

Entering SWITCH function
Entering SWITCH function

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.

Calculating SWITCH function
Calculating SWITCH function

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

Using fill handle to copy function
Using fill handle to copy function

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

Converted months to numbers using SWITCH
Converted months to numbers using SWITCH

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

Sample dataset 1

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

Define Name command
Define Name command

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

New Name dialog
New Name dialog

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

Formula Using VLOOKUP

Using a VLOOKUP formula
Using a VLOOKUP formula

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.

Using fill handle to copy VLOOKUP
Using fill handle to copy VLOOKUP

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

Converted months to numbers using VLOOKUP
Converted months to numbers using VLOOKUP

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

Get Data
Get Data

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.

Create table
Create table

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.

Month Name Custom Column
Month Name Custom Column

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.

Created a new column
Created a new column

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

Close and Load To
Close and Load To

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

Import Data
Import Data

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

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

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos πŸ˜ƒ