How to Get Month Name from Date in Excel (4 Easy Ways)

Excel allows you to format dates in many different ways. You can choose to show the date in a short date format or in a long date format.

You can also only show the day number, the month name, or the year from a given date.

In this short Excel tutorial, I will show you some easy methods to get the month name from a date in Excel.

So, let’s get started!

Getting the Month Name from the Date

There are multiple different ways to get monthly from a date in Excel.

The method you choose would depend on how you want the result (i.e., whether you want it as a text string or have the entire date but only show the name of the month)

Let’s see a couple of methods to do this that you can use in different scenarios.

Custom Formatting

Using custom number formatting to get the month name is the best method out of all those covered in this tutorial.

This is because it does not change the underlying date. It only changes the way the date is being displayed in the cell – which is by showing only the month name from the date.

The benefit of using this method is that you can still use the date in calculations.

Suppose you have the dates as shown below and you want to only display the month name and not the entire date.

Date Dataset

Below are the steps to do this:

  1. Select all the cells that have the dates for which you want to show the month name
  2. Click the Home tabClick the Home tab
  3. In the Number group, click on the dialog box launcher icon (or you can use the keyboard shortcut Control +1). This will open the Format Cells dialog boxClick on Format Cells open dialog box launcher
  4. In the Category options, click on CustomClick on Custom option in Fomat Cells dialog box
  5. In the type field, enter – ‘mmmm’. you should see one of the month names in the Sample preview.Enter mmmm as the custom number format
  6. Click OK

The above steps would convert all the dates into their respective full month names (as shown below).

Date showing only the month name

As I mentioned, the good thing about this method is that even though you are seeing the month names in the cell, the underlying value is still a date (which means that the underlying value is still a number that represents the date).

So, if you want to use these dates in calculations, you can easily do that.

You can also use custom number formatting to show the month name or the month value in different ways. To do this, you will have to give custom number formatting the right code to display the month name.

Below are the different month codes that you can use:

  • m – this will show the month number. For example, a date in January would be shown as 1, a date in February would be shown as 2, and so on
  • mm – this will also show the month number, but it will also make sure that there are always two digits that are displayed. For example, a date in January would be shown as 01, a date in February would be shown as 02, and a date in November would be shown as 11
  • mmm – this will show the month name in a three-letter code. For example, a date in January would be shown as Jan, a date in August would be shown as Aug, and so on
  • mmmm – this is the option that we used in the above example, and it would show the complete month name
  • mmmmm – this option shows only the first alphabet of the month name. For example, January is shown as J and February is shown as F, and so on. I’ve never seen this being used because it’s confusing as January would also show J and July would also show J

TEXT Function

TEXT function allows us to convert a date into any permissible format that we want and gives the result as a text string.

For example, we can use the TEXT function to show the month name from a date.

Now if you’re wondering how is it different from the custom number formatting we used earlier, the big difference here is that with the TEXT function, you can combine the result with other functions or text strings.

Don’t worry if you are a little lost as of now, the next few examples will make it clear.

Suppose you have a dataset as shown below and you want to show the month name instead of the full date.

Date Dataset

Below is the TEXT formula will give you the month name:

=TEXT(A2,"mmmm")

TEXT formula to get Month Name

The above text formula takes the date as the input and applies the specified format to it (which is “mmmm” in this formula).

Unlike the Custom Number Formatting method, when you use the TEXT function, the result is a text value. This means that you cannot use the result as a date or number in calculations.

But a good thing about using the TEXT function is that you can combine the result of the function with other text strings.

Let me explain using an example.

Suppose you have the same data set and in this case, instead of just getting the month name, you want to get the month name followed by the quarter number (such as January – Quarter 1).

The below formula would do this for you:

=TEXT(A2,"mmmm")&" - Quarter "&ROUNDUP(MONTH(A2)/3,0)

TEXT formula to combine text and formula result

The above formula uses the ROUNDUP and the MONTH function to get the quarter number of the calendar year, and then it is combined with the month name which is given by the TEXT function.

Since the result of the TEXT function is a text string, I can combine it with other text strings or formula results.

CHOOSE Function

Another formula that you can use to quickly get the month name from the month number is using the CHOOSE formula.

While it ends up being a long formula, the choose formula technique is useful when you want to get the result which you cannot get with custom number formatting or the text function.

For example, if you want to return custom month names (such as month names in any other language or only five alphabets for each month name), you can do that using the CHOOSE formula.

Suppose you have a data set as shown below and you want to get the month name for each of these dates.

Date Dataset

Below is the formula that will do that:

=CHOOSE(MONTH(A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Choose formula to get Month name

CHOOSE formula takes an index number (which is given by the month formula in our example) and then uses that index number to decide what value to return.

In this example, I have kept the month names to standard three-letter names, but you can change this and use whatever name you want.

Also read: Get Days in Month in Excel

Using Power Query

I have started using Power Query a lot more in my work as I find it a lot easier to clean the data using it.

It has an inbuilt feature that allows you to quickly convert a date into the month name.

The real value of using Power Query in such a scenario would be when you’re importing the data from other Excel files (or consolidating data from multiple Excel files into one file), and while doing it you want to convert your dates into month names.

You can check out my Power Query course on YouTube if you want to learn more about it.

For this technique to work, your data needs to be in an Excel table. While you can still use Power Query with named ranges, if your data is not in an Excel table I recommend you use any of the above methods.

Suppose you have the below data and you want to convert these dates into month names.

Excel Table with Dates

Below are the steps to do this:

  1. Select any cell in the dataset
  2. Click the Data tabClick the Data tab
  3. In the Get & Transform Data tab, click on From Table/RangeClick on From Table Range
  4. In the Power Query editor that opens up, right-click on the Date column header
  5. Go to Transform >> Month >> Name of MonthClick on Name of the Month in Power Query
  6. Click on Close and LoadClick on Close and Load

The above steps would insert a new worksheet and give you the resulting table in that new sheet.

Result with Month Name from Power Query

Now, if you’re wondering why do all this when you can simply use custom number formatting or the text function, you need to understand the real value of Power Query trying to automate work.

If you need the month name from the date just once, feel free to use the methods shown above.

But if you’re using Power Query already to manage data from multiple different sources or combined files or sheets, then knowing that you can easily get the month name from the date can save you a lot of time.

So these are some of the methods that you can use to quickly get the month name from a date in Excel.

I hope you found this tutorial useful.

Other Excel tutorial’s you may also like:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster