Converting Month Name to Number

Converting Month Name to Number. To obtain the number of a month from its name, you can utilize the DATEVALUE function to create a date with the desired month’s name, followed by using the MONTH function to extract the month number. The formula used for this purpose is illustrated below:

Formula for Obtaining Month Number from Month Name

You can follow these steps:

1. Begin by entering the DATEVALUE function in a cell.
2. Then, input “01” in the same cell.
3. Next, use an ampersand to combine with the cell containing the month name.
4. After that, use another ampersand and add “1900” to the end.
Finally, wrap the MONTH function around the DATEVALUE function, and press enter to view the outcome.

To comprehend this formula, you can divide it into two parts.

The first part employs the DATEVALUE function to create a valid date utilizing the month name. In this part, “01,” “January,” and “1900” are used, and DATEVALUE produces the date 01-Jan-1900.

The second part utilizes the MONTH function to extract the month number from the date obtained by the DATEVALUE function.

The advantage of utilizing this formula is that it works even if the month name is given in its complete form, such as “January,” rather than an abbreviated form like “Jan.”