top of page

How to Convert a Month Name to a Number in Excel


How to Convert a Month Name to a Number in Excel

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"))
formula to convert month name to month number

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"))
How to Convert Month Name to a Number using a formula


bottom of page