Usually, you need to return the last or first item in a list, but every once in a while, you may find yourself needing to return the second to last item in a list. These formulas will help.
Contents:
2. Explanation
3.1. How to Convert Month Name to Month Number
Formula
For this formula, the range should be replaced with the range of month names you would like to convert.
= MONTH(DATEVALUE(range & " 1"))
Explanation
This formula can be broken up into two main parts.
First, the DATEVALUE function converts a text date into number representing a date. This function needs the date string to be in a valid date format, which is why you append " 1" to the month name, creating a valid date string.
For example, if you have "January" in cell A1, A1 & " 1" will give you "January 1".
When this is fed to the DATEVALUE function, it is converted into a serial number representing the date.
So, DATEVALUE("January 1") will give you the serial number for January 1 in the current year: 1/1/2023 (Excel assumes the current year if you don't specify one).
Second, MONTH function then extracts the month number from this date serial number. So, if 1/1/2023 is fed into the MONTH function, a 1 will be returned. 2/1/2023 will return a 2, and so on.
Examples
How to Convert Month Name to a Number
In this example, we have a list of month names in column B and need to convert those names into the corresponding month numbers.
Using the following formula in column C, we can easily convert each name into a number.
This formula can be a single cell reference, or if you are using Excel 365, you can also select the entire range, using "B3:B14" as your range.
= MONTH(DATEVALUE(B3 & " 1"))