How to Get Month from a Date in Excel (Formula)

Last Updated: March 24, 2024
puneet-gogia-excel-champs

- Written by Puneet

Dates are an important part of data. There are a few times when we need to use only a part from a date. Take an example of the month. Sometimes you only need a month from a date.

A month is one of the useful components of a date that you can use to summarize data and when it comes to Excel we have different methods to get a month from a date.

I’ve found a total of 5 methods for this. And today, in this post, I’d like to share with you all these methods to get/extract a month from a date. So let’s get down to the business.

1. MONTH Function

Using the MONTH function is the easiest method to extract a month from a date. All you need to do is just refer to a valid date in this function and it will return the number of the month ranging from 1 to 12.

=MONTH(A2)

get month from a date with month function

You can also insert a date directly into the function using the correct date format.

Pro
Con
It’s easy to apply and there is no need to combine it with other functions.
Month number has limited use, most of the time it’s better to present the month name instead of numbers and if you want the month name then you need a different method.

2. TEXT Function

As I said, it’s better to use a month name instead of a month number. 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")
get month from a date with text function

By default, you have 5 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. Yes, that’s all. And, make sure the date you are using should be a valid date as per Excel’s date system.

Pros
Cons
You have the flexibility to choose a format among 5 different formats and the original date column will remain the same.
It will return the month name which will be a text and using a custom abbreviation for the month name is not possible.

3. CHOOSE Function

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")
data with month name from a date

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

Pros
Cons
It allows you more flexibility and you can specify a custom month name for each month.
It’s a time-consuming process to add all the values in the function one by one.

4. Power Query

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”.
click on from table to get month from date with power query
  • 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”.
add a duplicate column to get month from a date
  • Select the new column from the heading and right-click on it.
  • Go to Transform ➜ Month ➜ Name of Month or Month.
select name of month to get month
  • It will instantly convert dates into months.
click close and load to get month from a date
  • 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.
data with month name from a date
Pro
Con
It’s a dynamic and one-time set-up. Your original data will not get impacted.
You should have a power query in your Excel version and the name of the month will be as a full name.

5. Custom Formatting

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.

  1. Select the range of cells or the column.
  2. Press the shortcut key Ctrl + 1.
  3. From the format option, go to “Custom”.
  4. Now, in the “Type” input bar enter “MM”, “MMM”, or “MMMMMM”.
  5. Click OK.
get month from a date with conditional formatting open formatting options
Pros
Cons
It’s easy to apply and doesn’t change the date but its format.
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.

Conclusion

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.

Get the Excel File

6 thoughts on “How to Get Month from a Date in Excel (Formula)”

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

    Reply

Leave a Comment