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.


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

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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 ...
How to Use Dates in Excel
Tutorial: Introduction Guide to using Dates in Excel - this tutorial will show you how to input and...
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...
Tutorial Details
Excel Function: DATE(), DATEVALUE(), MONTH(), TEXT()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course