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. You can see in the following example the formula that we have used.
Formula to Get the Month Number from the Month Name
You can use the following 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.
Download Sample File
- Add Month to a Date in Excel
- Get the End of the Month Date in Excel
- Get the First Day (Beginning) of the Month in Excel
- Get Month from a Date in Excel
- Get Quarter from a Date [Fiscal + Calendar] in Excel
- Months Between Two Dates in Excel
- Extract (Get) Year or Month from a Date in Excel