Explanation
In this example, the goal is to return a number, 1-12, for any month name of the year.
For example, given the string “January” we want to return 1, “February” should return 2, and so on.
If we had a validExcel date, we could use anumber formatfor this task, but because we are starting with atext string, we need another way.
The approach we take in this example is to create a date fragment that can be correctly interpreted by Excel as a valid date.
In the example shown, the formula in cell C5 is:
Working from the inside out, we start byconcatenatingthe name in cell B5 to the number 1:
This expression returns a string like “January1”, “February1”, “March1”,and so on.
It turns out, that if we pass a date fragment like this into theMONTH function, it will coerce the string to a valid date, using the current year.
As I write this, the year is 2021, so the result for January is the date January 1, 2021, represented in Excel as the serial number 44197.
After this date has been created, the MONTH function returns the correct month number for the date that was created using concatenation.
In each case, the actual date value is a “throwaway”, used only as a convenient value to pass into the MONTH function.
In B5 the evaluation works like this (in the year 2021):
Date evaluation
This example is a bit tricky in that the evaluation of the date inside the MONTH function is automatic.
If you use the expression that concatenates the month name to 1outsidethe MONTH function, you’ll need to add an extra step to get Excel to convert the text to a date.
TheDATEVALUE functionor adding zero are both good options:
Both will return a date serial number, which must then beformattedas a date.
Related formulas
Get month name from date
Get month from date
Get day name from date
Convert date to text
Convert text to date
Related functions
MONTH Function
The Excel MONTH function extracts the month from a given date as a number between 1 and 12. it’s possible for you to use the MONTH function to extract a month number from a date into a cell or to feed a month number into another function like theDATE function….
DATEVALUE Function
The Excel DATEVALUE function converts a date represented as atext stringinto avalid Excel date.
For example, the formula =DATEVALUE(“3/10/1975”) returns a serial number (27463) in the Excel date system that represents March…
