Let’s say you work in an office and manage project deadlines. You have a list of due dates for each project, and you need to plan your team’s workload by month.
Using an Excel formula to convert dates into month names, you can easily group all the projects due in the same month together.
In this tutorial, we will discuss all the available formulas and other methods you can use to get the month name from the date.
Extract Month Name using the TEXT
Using the TEXT function is a perfect method to extract the month name from a date. The basic work of the text function here is to convert a date into a month by using a specific format.
=TEXT(A2,"MMM")
You have different date formats which you can use in the text function. These formats will return the month name as a text.
All you need to do is refer to a date in the function and specify a format. And, make sure the date you are using should be a valid date as per Excel’s date system.
- PRO – You have the flexibility to choose a format among 5 different formats and the original date column will remain the same.
- CONS – It will return the month name which will be a text and using a custom abbreviation for the month name is not possible.
If you want to get the month name with a text in a cell to make it more meaningful, you can combine the TEXT function with CONCATENATE and an ampersand.
="The month is " & TEXT(A1, "mmmm")
Use CHOOSE to Get Month from Date
Let’s say you want to get a custom month name or maybe a name in a different language instead of a number or a normal name. In that situation, CHOOSE function can help you.
You need to specify a custom name for all 12 months in the function and need to use the month function to get the month number from the date.
=CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
When the month function returns a month number from the date, choose function will return the custom month name instead of that number.
Related: Formula Bar
- PRO – It allows you more flexibility and you can specify a custom month name for each month.
- CON – It’s a time-consuming process to add all the values in the function one by one.
Create a Custom Function for Get Month Name from a Date
You can also write a code to create a custom Excel function. This custom function gives you more flexibility to choose if you want a long name of the month or a short one. Below is the code which you need to enter in the VBA Editor.
Function GetMonthName(dateValue As Date, fullName As Boolean) As String
If fullName Then
GetMonthName = MonthName(Month(dateValue), False) ' Returns the full month name
Else
GetMonthName = MonthName(Month(dateValue), True) ' Returns the abbreviated month name
End If
End Function 
In this function, dateValue is the cell (or the date you want to enter into the formula directly) that contains the date from which you want to extract the month name. And, in the fullName, use TRUE if you want the full name of the month (e.g., “January”), or FALSE for the short name (e.g., “Jan”).
Apply Custom Formatting for Month Name
If you don’t want to get into any formula method, the simple way you can use it to convert a date into a month is by applying custom formatting. Follow these simple steps.
- Select the range of cells or the column.
- Press the shortcut key Ctrl + 1.
- From the format option, go to “Custom”.
- Now, in the “Type” input bar enter “MM”, “MMM”, or “MMMMMM”.
- Click OK.
- PRO – It’s easy to apply and doesn’t change the date but its format.
- CON – As the value is a date, when you copy and paste it somewhere else as a value it will be a date, not text. If someone changes the format, the month’s name will be lost.
Use Power Query to Get Month Name from a Long List of Dates
If you want to get a month’s name from a date in a smart way then power query is your calling. It can allow you to convert a date into a month and extract a month number or a month name from a date as well. Power Query is a complete package for you to work with dates. Here you have the below data and here you need months from dates.
- First of all, convert your data into a table.
- After that, select any of the cells from the table and go to the data tab.
- From the data tab, click on “From Table”.
- It will load your table in the power query editor.
From here, you have two different options one is to add a new column with the month name or month number or convert your dates into a month name or month number. Skip the next two steps if you just want to convert your dates into months without adding a new column.
- First of all, right-click on the column heading.
- And after that, click “Duplicate Column”.
- Select the new column from the heading and right-click on it.
- Go to Transform ➜ Month ➜ Name of Month or Month.
- It will instantly convert dates into months.
- Just one more thing, right-click on the column heading and rename the column to “Month”.
- In the end, click on “Close & Load” and it will load your data into a worksheet.
- PRO – It’s a dynamic and one-time set-up. Your original data will not get impacted.
- CON – It’s a dynamic and one-time set-up. Your original data will not get impacted.
Wrap Up
All the above methods can be used in different situations. But some of them are more frequently used.
TEXT function is applicable in most situations. On the other hand, you also have a power query to get months in a few clicks. If you ask me, I use the text function but these days I am more in love with power query so I would also like to use it.
In the end, I just want to say, you can use any of these methods according to your need. But you need to tell me one thing.
Which one is your favorite method? Please share with me in the comment section, I’d love to hear from you. And, don’t forget to share this with your friends.
Is this the same one as the one highlighted in your recent emaiil, How to Get Month From a Date where the links do not work?
Thanks Puneet
Yes 🙂
I love to use text function and custom formating.
That’s great.
This is great! Do you have any tutorials on Power Query?
Have a plan to write it soon. 🙂