Convert Month Name to Number and Vice Versa in Excel

Add to Favorites
Author: | Edits: don

How to convert a month's name, such as January, into a number using a formula in Excel; also, how to do the reverse, convert a number into the name of the month.

Sections:

Convert Month Name to Number

Convert Month Number to Name

Notes

Convert Month Name to Number

=MONTH(DATEVALUE(A2&1))

(Alternate formula - not as robust)

=MONTH(A2&1)

271543fecb352d137ab0b289797a497f.png

Result:

e50ad41ea13337c40757bf8c4e3abcb7.png

Copy the formula down for the entire list and it will work without issue.

How the Formula Works

DATEVALUE converts the date from a text format to a readable Excel format; however, we have to attach a 1 to the name of the month in order for this to work, which is why there is &1 in the formula. This tells the DATEVALUE function to get the date for, for instance, January 1st.

MONTH function takes the date returned by the DATEVALUE function and strips out everything except for the month. This is what returns the actual number for the month.

3 Letter Month Names

If you have 3 letter month abbreviations, this formula will work the same:

8eade7b9281ac850336f7999d212a086.png

Convert Month Number to Name

=TEXT(DATE(2018,B2,1),"mmmm")

fe1bb29ff022ed7298e3c95ca8268a67.png

Result:

08e7f30af9f9e7682a57af821fd4caf3.png

How the Formula Works

DATE(2018,B2,1) gets an actual date from the single number that represents the month.

2018 is a placeholder for the year; it doesn't matter what year you use.

1 is a placeholder for the day of the month.

B2 is the cell that contains the number of the month.

DATE is the function that combines the previous values to create a working date in Excel.

TEXT is the function that takes the working date and pulls out the text name for the month.

mmmm is what tells the TEXT function to return the full name of the month. If you replace this with only 3 m's like this mmm then the abbreviated version of the month will be returned.

3 Letter Month Names

=TEXT(DATE(2018,B3,1),"mmm")

The difference here is that there are three m letters instead of four in the TEXT function.

Notice mmm instead of mmmm

051162d46c9cfc8f607938ddf71945ea.png

Get Month Name from Full Date

If you are starting with a complete date instead of just the number of the month, you can easily get the name of the month like this:

=TEXT(E2,"mmmm")

d6b0bf1ac7cfc1401e44de5bbf594ec1.png

Result:

52354102d0b11121a88cee85f4c3c849.png

Notes

Once you get a list of month names or numbers, you will often want to "freeze" that data. In this case, you can copy/paste-special values to quickly remove all formulas and keep only the visible values. To do this, select the desired cells and hit Ctrl+C and then Alt+E+S+V Enter.

Make sure to download the attached workbook to view these examples in Excel.

Question? Ask it in our Excel Forum


Excel Function: DATE(), DATEVALUE(), MONTH(), TEXT()
Downloadable Files: Excel File

Similar Content on TeachExcel
Convert Time to Minutes and Vice Versa in Excel
Tutorial: How to convert a time into minutes in Excel and also how to get minutes back into a time f...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Make Negative Numbers Positive in Excel and Vice Versa
Tutorial: I will show you a few ways to change negative numbers to positive numbers and back again ...
Convert Scientific Notation to Numbers in Excel
Tutorial: How to convert scientific numbers to show their full amount; this method also allows you t...
Create a 12 Month Calendar With The Current Day Highlighted in Excel
Macro: Create a 12 month formatted calendar on a new tab in Excel with the current day highlighte...
Return the ISO Week Number from a Date in Excel - UDF
Macro: Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defin...
Tutorial Details
Excel Function: DATE(), DATEVALUE(), MONTH(), TEXT()
Downloadable Files: Excel File
Similar Content
Convert Time to Minutes and Vice Versa in Excel
Tutorial: How to convert a time into minutes in Excel and also how to get minutes back into a time f...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Make Negative Numbers Positive in Excel and Vice Versa
Tutorial: I will show you a few ways to change negative numbers to positive numbers and back again ...
Excel Forum