How to Change Month from Text to Number in Excel

Excel has many helpful tips and tricks that people are usually not aware of. One of them is to convert the name of the months into numbers.

Although you might think that this issue could be formatting-related, it is actually a matter of using the correct formula. We will show that in the example below.

Change Months from Text to Numbers

For our example, we will use the table with months that are not ordered chronologically:

Text, table

Description automatically generated

Now, we will put the following formula into the cell B2:

=MONTH(DATEVALUE(A2&"1"))

To explain how this works, we have to explain formulas separately:

DATEVALUE is a function that returns a serial number from a date that is in the text format.

We have to use the „&“ symbol and any number to make Excel convert the text into the number.

MONTH function returns the value of the month from 1 to 12.

So, with our formula, we make Excel recognize the number of our month and then return this number to our cell.

When we drag the formula to the end of our range, our table will look like this:

A picture containing chart

Description automatically generated

You will notice that this formula returned the proper values for every month in our table.

If we had the whole date, to find out the serial number of the date, we can use the MONTH formula as a standalone one, like in the example below:

=MONTH(D2)
Timeline

Description automatically generated