This tutorial showcases the most efficient formulas for converting a month name to a month number and vice versa in Excel.
Additionally, we’ll explore how to convert a month name and year to the beginning of the month date.
Month Name to Month Number in Excel
If cell A1 contains the full or abbreviated month name, you can use the following formula in any other cell to convert it to the corresponding month number:
=MONTH(A1&1)
No need to do the heavy lifting of creating a table with month names in one column and corresponding month numbers in another column. Then, you’d have to perform a lookup in column 1 using either VLOOKUP, XLOOKUP, or LOOKUP, and return the value from the second column.
Formula Breakdown
When you enter a month name followed by 1 in a cell, Excel returns a date. For example, entering April1 in any cell results in Excel returning the date 01-04-2001.
From this date, we can extract the month number using the MONTH function, which is precisely what this formula accomplishes:
=MONTH("April"&1)This is equivalent to (if A1 contains “April”):
=MONTH(A1&1)Notes
- If A1 is blank, the formula will return 1 because Excel interprets empty cells as 0. When formatted as a date, Excel defaults empty cells to 00-01-1900. When concatenated with 1, the empty cell effectively becomes “01”. Consequently, the MONTH function interprets “01” as 01-01-1900, returning the month serial number 1.
- If A1 contains text other than month names, the formula will return a #VALUE error.
- If cell A1 contains a date, the formula may return an incorrect month number.
Troubleshooting
You can utilize an IF logical test to address an invalid result when converting a month name to a month number in Excel. Replace A1 with IF(ISNONTEXT(A1), "x", A1).
=MONTH(IF(ISNONTEXT(A1), "x", A1)&1)This adjustment ensures that if cell A1 is blank, or contains a date or a number, it will be converted to “x”. Additionally, wrap the formula with IFERROR as follows:
=IFERROR(MONTH(IF(ISNONTEXT(A1), "x", A1)&1),"")Month Name and Year to a Date in Excel
How can we convert a month name in one cell and a year in another cell to the starting date of that month in that year?
We can utilize the DATE function in Excel to achieve this task by providing the Year, Month, and Day (all as numbers) to form a date. The syntax for the DATE function is DATE(year, month, day).
If cell A1 contains the month name and cell B1 contains the year, you can employ the following formula:
=DATE(B1, MONTH(A1&1), 1)
Explanation:
Year: B1Month:MONTH(A1&"1")(the month text converted to a month number)Day: 1
This approach enables us to convert a month name and year to a date in Excel. You’ll find this technique useful in various scenarios.
Month Number to Month Name in Excel
If cell A1 contains the month number, you can obtain the corresponding month name using the following formulas in Excel:
=TEXT(DATE(2000, A1, 1), "MMMM") // full length month name
=TEXT(DATE(2000, A1, 1), "MMM") // abbreviated month name
How does this formula work in Excel?
The DATE function constructs a date using the provided month number in Excel. Then, the TEXT function formats that date into a month name.
Notes
- The formula will return an error if the value in cell A1 is text.
- It may return month names even if the numbers are not between 1 and 12. For example, a 0 will yield “December”. Excel adjusts invalid month numbers in the DATE function, so
=DATE(2000, 1, 1)will return 01-01-2000, whereas=DATE(2000, 0, 1)will return 01-12-1999.
Troubleshooting
To address the above error, you can employ an IF logical test to evaluate the value. Replace A1 in the formula with IF(OR(A1>12, A1<1),"x", A1). This will convert any numbers that do not fall within the range of 1 to 12 to “x”.
So the formula will be:
=TEXT(DATE(2000, IF(OR(A1>12, A1<1),"x", A1), 1), "MMMM")Additionally, wrap the formula with IFERROR as follows:
=IFERROR(TEXT(DATE(2000, IF(OR(A1>12, A1<1),"x", A1), 1), "MMMM"), "")
