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

Get Month from a Date

how to get month from a date in excel

Dates are an important part of data. It can help you to capture your data in a better way and it’s easy to use dates in further analysis. And, the best part is you can also modify dates as well and use them in your own way.

Normal dates, when you are working in excel or anywhere else, are made up of three components. That’s day, month and year. If you think with usage perspective all these three components are important for you.

But, in the real world, it happens sometimes when you need to use only one of these components. And in that case, you need to extract that particular component from a date.

And today, in this post, you will learn some useful methods to get 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.

But before you explore these methods, you need to understand that you can use a month from a date in two different ways, one is you can extract a month value or you can just convert your date into a month by changing its format.

I have listed all these methods here and you can use all of them or any of them which you think is perfect for you.

Below is a list of dates from which you need to get month values. So, please download this file from here to follow along.

data to get month from a date

And, let’s get started.

Table of Content

  1. Use Month Function
  2. Text Function
  3. Choose Function
  4. Power Query
  5. Custom Formatting

1. Use Month Function for Month Number

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

get month from a date with month function

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

Pros:

  1. It’s easy to apply.
  2. No need to combine with other function.

Cons:

  1. Month number has a limited use, most of the time it’s better to present month name instead of numbers.
  2. If you need month name then you need to use some other functions with it.

Learn more about MONTH Function from here.

2. Get Month Name with Text Function

As I say, it’s better to use month name instead of a month number. 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.

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.

All you need to do, refer 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:

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

Cons:

  1. It will return month name which will be a text. You can’t use it in further calculations.
  2. Using a custom abbreviation for the month name is not possible.

3. Get Month Name With Choose Function

Now, let’s say you want to get a custom month name or may be a name in different language instead of a number or a normal name.

In that situation, CHOOSE function can help you.

Learn more about CHOOSE from here.

You need to specify a custom name for all the 12 months in the function and need to use the month function to get month number from the date.

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:

  1. It allows you more flexibility.
  2. You can specify a custom month name for each month.

Cons:

  1. It will return month name which will be a text. You can’t use it in further calculations.
  2. It’s a time-consuming process to add all the values in the function.

4. Get Month Number and Name With 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 first 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:

  1. Power query is dynamic and one-time set-up.
  2. Your base data will not impact.

Cons:

  1. You should have power query in your Excel version.
  2. Name of the month will be as a full name.

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

It’s simple and easy.

Follow these steps to apply custom formatting.

  • Select the range of cells or the column.
  • Press shortcut key Ctrl + 1.
  • From the format option, go to “Custom”.
  • Now, in “Type” input bar enter “MM”, “MMM” or “MMMMMM”.
  • Click OK.

get month from a date with conditional formatting open formatting options

Now, you have month name in the column instead of dates.

Pros:

  1. Easy to apply.
  2. It will not change the date but its format. You can use these dates further into calculations.

Cons:

  1. As the value is a date, when you copy and paste it somewhere else as values it will be a date, not text.
  2. 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, I am sure you definitely have a favorite method which you use every time.

And, I want you o share that method with me in the comment section, I would love to hear from you.



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