Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

How to Get Month from a Date in Excel

Dates are an important part of data.

There are 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 which 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 total 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

=MONTH(A2)

get month from a date with month function

Pros

It’s easy to apply and there is no need to combine with other functions.

Cons

Month number has a limited use, most of the time it’s better to present month name instead of numbers and if you want month name then you need a different method.

#2. TEXT Function

As I said, it’s better to use month name instead of a month number. Using TEXT function is a perfect method to extract the month name from a date.

The basic work of 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 month name as a text.

Pros

You have the flexibility to choose a format among from 5 different formats and the original date column will remain same.

Cons

It will return month name which will be a text and using a custom abbreviation for the month name is not possible.

#3. CHOOSE Function

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

data with month name from a date

So, when month function returns a month number from the date, choose function will return the custom month name instead of that number.

Pros

It allows you more flexibility and you can specify a custom month name for each month.

Cons

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 month name from a date in a smart way then Power Query is your calling.

It can allow you to convert a date into the month and extract a month number or a month name from a date as well.

So, power query is a complete pack for you to work with dates. Here you have 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 data tab.
  • From data tab, click on “From Table”.
click on from table to get month from date with power query
  • It will load your table in 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 next two steps if you just want to convert your dates into month 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 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

Pros

It’s a dynamic and one-time set-up. Your original data will not get impacted.

Cons

You should have 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 to convert a date into a month is by applying a custom formatting.

Follow these simple steps.

  1. Select the range of cells or the column.
  2. Press shortcut key Ctrl + 1.
  3. From the format option, go to “Custom”.
  4. Now, in “Type” input bar enter “MM”, “MMM” or “MMMMMM”.
  5. Click OK.
get month from a date with conditional formatting open formatting options

Pros

It’s easy to apply and doesn’t change the date but its format.

Cons

As the value is a date, when you copy and paste it somewhere else as values it will be a date, not text. If someone changes the format, month name will be lost.

Conclusion

All above methods can be used in different situations. But some of them are more frequently used.

TEXT function is applicable in most of the situations. On the other hand, you also have power query to get months in few clicks.

If you ask me, I use text function but these days I am more in love 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.

  • Lisa Gordon Delgado

    This is great! Do you have any tutorials on Power Query?

    • Puneet Gogia

      Have a plan to write it soon. 🙂

  • Ogundepo Ezekiel Adebayo

    I love to use text function and custom formating.

    • Puneet Gogia

      That’s great.