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.


Convert Month Name to Number

Convert Month Number to Name


Convert Month Name to Number


(Alternate formula - not as robust)





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:


Convert Month Number to Name





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


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

Notice mmm instead of mmmm


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:






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

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...
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...