How to convert a month's name, such as January, into a number using a formula in Excel; also, how to do the reverse, convert a number into the name of the month.
=MONTH(DATEVALUE(A2&1))
(Alternate formula - not as robust)
=MONTH(A2&1)
Result:
Copy the formula down for the entire list and it will work without issue.
DATEVALUE converts the date from a text format to a readable Excel format; however, we have to attach a 1 to the name of the month in order for this to work, which is why there is &1 in the formula. This tells the DATEVALUE function to get the date for, for instance, January 1st.
MONTH function takes the date returned by the DATEVALUE function and strips out everything except for the month. This is what returns the actual number for the month.
If you have 3 letter month abbreviations, this formula will work the same:
=TEXT(DATE(2018,B2,1),"mmmm")
Result:
DATE(2018,B2,1) gets an actual date from the single number that represents the month.
2018 is a placeholder for the year; it doesn't matter what year you use.
1 is a placeholder for the day of the month.
B2 is the cell that contains the number of the month.
DATE is the function that combines the previous values to create a working date in Excel.
TEXT is the function that takes the working date and pulls out the text name for the month.
mmmm is what tells the TEXT function to return the full name of the month. If you replace this with only 3 m's like this mmm then the abbreviated version of the month will be returned.
=TEXT(DATE(2018,B3,1),"mmm")
The difference here is that there are three m letters instead of four in the TEXT function.
Notice mmm instead of mmmm
If you are starting with a complete date instead of just the number of the month, you can easily get the name of the month like this:
=TEXT(E2,"mmmm")
Result:
Once you get a list of month names or numbers, you will often want to "freeze" that data. In this case, you can copy/paste-special values to quickly remove all formulas and keep only the visible values. To do this, select the desired cells and hit Ctrl+C and then Alt+E+S+V Enter.
Make sure to download the attached workbook to view these examples in Excel.