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.
To write this formula you can use the below steps
- 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.
- Now, again use an ampersand and enter “1900”.
- 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.
In the SECOND part, the MONTH function returns the month number from the date returned by the DATEVALUE.
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.
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