How to Extract Month From Date in Excel: Step-by-Step (2024)

Have you ever found yourself struggling to extract the month from a date in Excel?

You may need to sort your data by month or create a pivot table based on the month. But you just can’t seem to figure out how to extract that information.

Don’t worry; we’ve got you covered! 🧐

In this step-by-step guide, we’ll show you exactly how to extract the month from a date in Excel. So you can get the insights you need from your data in no time.

Click here to download our sample workbook for extracting a month from date in Excel.

Table of Contents

Extract month name from date

Extracting month name from a date is pretty easy, and we will use the TEXT function to find it. Its syntax is as below:

=TEXT(value, format_text)

Where:

  • value: the value or cell reference that you want to convert to text.
  • format_text: the format that you want to apply to the value. This can be a number, date, or custom format.

Let’s see how to use it below 😉

Say, we have the following sample data.

Sample data for extracting month name from date - only the year

We want to find the month name of each date value in the data set.

To do that,

  1. Select cell B2.
  2. Enter the TEXT function as:

=TEXT(

Enter the Text function - format cells dialog box
  1. Enter the cell reference containing the underlying value.

=TEXT(A2,

Enter the cell reference for Text formula - choose function
  1. Enter the format

=TEXT(A2, “mmm”) 

Enter the format for Text function - index number

PRO TIP!

This format returns the name of the month as Jan. If you want it to return the entire name, use the following formula

=TEXT(A2,”mmmm”)

This long date format will return the name of the month as January.

  1. Press Enter.

The TEXT function will return the value as:

The result appears as Jan - power query editor

Double-click or drag the Fill Handle to copy the values to the remaining cells.

Names of months appear in the result - switch function

Pretty cool, no? 🤩

Extract month number from date

Extracting a month’s number from a date is even simpler than what we saw above. For this purpose, we use the MONTH function. Its syntax is as below:

=MONTH(serial_number)

Where

  • serial_number is the date value from which you want to extract the month number.

Let’s now see how to use it below 😃

We will use the same sample data as used above.

To extract the month number from a date,

  1. Select cell B2.
  2. Enter the MONTH function as:

=MONTH(

Enter the month function as above formula
  1. Enter the cell reference as:

=MONTH(A2)

Enter the cell reference for month functions - Excel table
  1. Press Enter.

The number of the month from the date appears as follows:

The result appears using Month formula in formula bar

Double-click the Fill Handle to copy the MOTNH formula to other cells.

The month numbers of all dates appear in month value data tab

And voila! We did it 🎯

Try it yourself using the sample workbook now.

That’s it – Now what?

In this article, we saw how to use the MONTH and TEXT functions. We saw how to extract the name and month number from date in easy and quick steps.

By now, you should be a pro at extracting the month from a date in Excel!

Whether sorting data or creating a pivot table, being able to extract the month is an essential skill. With the guide we’ve provided, you’ll be able to do it in no time 😎

All the while allowing you to focus on analyzing your data and gaining valuable insights. So why wait? Start putting your newfound Excel skills to use today!

Excel has other important functions, too, apart from the MONTH function. These are IF, SUMIF, VLOOKP, etc.

You can learn them for free in my 30-minute free email course that teaches this and more. You can get it only at the cost of your email address. So sign up now!

Other resources

Did you enjoy reading this article? If yes, then you’d love to read more.

Try similar topics: Time and Date Functions, First Day of the Month, Number of Days in the Month, and more.