How to Convert Month Name to Number in Excel

puneet-gogia-excel-champs

- Written by Puneet

Say you’re an event planner and receive a list of events and the months they’re scheduled for, like “March” for a spring festival. Now, your budget Excel sheet uses month numbers instead of names.

In Excel, you can convert a month name, like, “March” into “3” and “October” into “10,” using multiple formulas.

To convert a month’s name into the month’s number, you need to create a date with that month’s name using a DATEVALUE function and then use the MONTH function to get the number of the month.

Formula to Get the Month Number from the Month Name

In this formula, you need to combine MONTH and DATEVALUE functions. With this you can take the month name and create a date, and then get month number from that date into cell.

convert-month-name-to-number

To write this formula you can use the below steps

  1. First, enter the DATEVALUE function in a cell. After that, you need to enter “01”. Next, use an ampersand and refer to the cell where you have the month’s name.
  2. Now, again use an ampersand and enter “1900”.
  3. In the end, wrap the MONTH function around the DATEVALUE and hit enter to get the result.

To understand this formula, you can break it down into two parts. In the FIRST part, you have the DATEVALUE, which helps you to create a valid date using the month name. You have 01, January, and 1900 and DATEVALUE creates the date 01-Jan-1900.

2 first part has datevalue

In the SECOND part, the MONTH function returns the month number from the date returned by the DATEVALUE.

second-part-has-month-function

The benefit of using this formula is that if you have the month in the full form, like “January” instead of “Jan”. This formula will still work.

benifits-of-month-and-datevalue

Convert Month Name into a Number with MATCH

MATCH helps you find an item’s position in a list. You provide the item you’re looking for and the list where the formula searches.

Excel then tells you where in the list (like the first, second, third spot, etc.) that item appears.

=MATCH(A1, {"January","February","March","April","May","June","July","August","September","October","November","December"}, 0)
=MATCH(A1, {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, 0)

Here, A1 contains the month name. The MATCH function searches for the month name within the array of all month names and returns its position (i.e., the month number).

This formula converts a month name into a date and then extracts the month number from it. Same thing can also be achieved by combining CHOOSE with MATCH. See the formula below:

=CHOOSE(MATCH(A1, {"January","February","March","April","May","June","July","August","September","October","November","December"}, 0), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)

And here’s one more formula, that uses a combination of MATCH + ISNUMBER + SEARCH.

=MATCH(TRUE, ISNUMBER(SEARCH({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, A1)), 0)

Get Month Number from Name with VLOOKUP

Yes, you can also use VLOOKUP to create an array of all the month’s names and then look up the number to get that month’s number.

=VLOOKUP(A1, {"January",1; "February",2; "March",3; "April",4; "May",5; "June",6; "July",7; "August",8; "September",9; "October",10; "November",11; "December",12}, 2, FALSE)

As I said, it helps you find the month’s number from its name.

You enter a month name into cell A1, like “March.” Excel then looks through a predefined list where each month’s name is looked up with its number. It finds “March” and returns “3,” the number for March.

=VLOOKUP(A1, {"Jan",1; "Feb",2; "Mar",3; "Apr",4; "May",5; "Jun",6; "Jul",7; "Aug",8; "Sep",9; "Oct",10; "Nov",11; "Dec",12}, 2, FALSE)

Use XLOOKUP to Get the Month Name from a Number

XLOOKUP helps you find a value from a list or table. You can specify the lookup value, where to look for it, and what corresponding information to return in the result.

=XLOOKUP(A1, TEXT(DATE(0, SEQUENCE(12), 1), "mmmm"), SEQUENCE(12))

Like other formulas, this formula helps you convert the month name into a number.

When you type a month name into cell A1, like “July”, it looks at a list it creates of all month names (from January to December) using TEXT(DATE(0, SEQUENCE(12), 1) and matches the name you have in the cell A1.

In the end, it shows the corresponding month number, so if you have “July,” it will return “7.”

Create a Custom Function for Month Name to Number

If you are someone who doesn’t want to write formulas again and again, you can use the macro below to create custom function that can take month name (short and long) and then convert it into a number.

Function MonthNameToNumber(monthName As String) As Integer
    ' This function converts a full month name or its abbreviation to the corresponding month number.

    ' Convert the input to proper case to standardize comparisons
    monthName = Application.WorksheetFunction.Proper(monthName)

    ' Use Select Case to determine the month number based on monthName
    Select Case monthName
        Case "January", "Jan"
            MonthNameToNumber = 1
        Case "February", "Feb"
            MonthNameToNumber = 2
        Case "March", "Mar"
            MonthNameToNumber = 3
        Case "April", "Apr"
            MonthNameToNumber = 4
        Case "May"
            MonthNameToNumber = 5
        Case "June", "Jun"
            MonthNameToNumber = 6
        Case "July", "Jul"
            MonthNameToNumber = 7
        Case "August", "Aug"
            MonthNameToNumber = 8
        Case "September", "Sep"
            MonthNameToNumber = 9
        Case "October", "Oct"
            MonthNameToNumber = 10
        Case "November", "Nov"
            MonthNameToNumber = 11
        Case "December", "Dec"
            MonthNameToNumber = 12
        Case Else
            ' Return 0 if the monthName is not recognized
            MonthNameToNumber = 0
    End Select
End Function

To use this custom function:

  • Press ALT + F11 to open the VBA editor.
  • Go to Insert > Module to create a new module.
  • Paste the code there and close the VBA editor.

Now, you can use this function just like any other Excel function. If you have a month name in cell A1 ), you can type =MonthNameToNumber(A1) in another cell to get the result.

Which Formula is the Best?

The best formula to convert a month name to a month is your situation, but if you ask me, I’d say there are two formulas that I have found the best when it comes to:

  • MONTH and DATEVALUE
  • Custom Function with VBA
Last Updated: May 09, 2024