Being able to work with dates is an essential skill for anyone working in Excel. This is true, regardless of industry. For example, in project management you may need to be able to work with date duration. In finance and accounting, you’ll often need to aggregate data across different periods of times. In this tutorial, you’ll learn how to extract the month from a date in Excel.
By the end of this tutorial, you’ll have learned the following:
- How to extract the month from a date in Excel
- How to get the month number from a date in Excel
- How to display the weekday name in Excel but maintain date information
What Method is the Best to Extract a Month from a Date in Excel?
In this tutorial, you’ll learn three different methods to extract a month from a date in Excel, including:
- Using the TEXT() function to extract the month name for a given date
- Using the MONTH() function to get the month number from a date
- Using Excel formatting to display a month name for a given date
While other tutorials may point to many different methods in order to get the month from a date. A lot of these methods are inconvenient and purely academic. In this tutorial, you’ll learn the three best methods to extract the month from a date in Excel.
Each of these methods provides a different benefit. Let’s break these benefits down in the table below:
| Method | Best For… |
|---|---|
| TEXT() | Getting the month name as a text value |
| MONTH() | Getting the month as a number, ranging from 1 through 12 |
| Excel Formatting | Keeping date information but only changing how the date is displayed |
Now you’re probably thinking, “Great – but how do I do any of this?!” Don’t worry, we’re diving into that right now!
How to Extract the Month from a Date in Excel Using TEXT()
How can you extract the month from a date using Excel?
In order to extract the month from a date in Excel, use this formula: =TEXT(A2, “mmmm”). Using “mmmm” will return the entire month name, so that November 23, 2023 returns November. To return an abbreviated version, you can pass in “mmm”, which returns a three letter abbreviation, so that November 23, 2023 returns Nov.
Let’s take a look at how this works in Excel, by working through a step by step example.
Step 1: Load Your Dataset in Excel
In this example, we’ll use a dataset that contains seven days. The dates come from a variety of years, just to demonstrate how it works.
Step 2: Use the TEXT() Function to Get the Month Name
By passing in a date value into the Excel TEXT() function, we can customize how the date should be represented. For example, by passing in "mmm", as we did above, the formula returns an abbreviated version of the month. For example, November is represented as Nov and December is represented as Dec.
Do you want to show full month names instead?
If you want to show the full month name, rather than an abbreviated version, you can pass in "mmmm" into the TEXT() function instead. This will load the full month name, localized to your version of Excel.
Step 3: Use the Fill Handle to Copy the Formula Down
By clicking and dragging the fill handle, you can easily copy and paste the formula for multiple cells. When you drag the formula down for the entire range of values, Excel will extract the month name from each of the cells in the dataset.
In the following section, you’ll learn how to use the Excel MONTH() function to get the number of the month, rather than its name.
How to Extract the Month Number from a Date in Excel Using MONTH()
There may be times when you simply want to represent the month using a number. This can be really helpful for determining whether or not a month belongs to a certain quarter. For example, if a month number is between 1 and 3, you can classify it as Quarter 1 using an IF() function.
The Excel MONTH() function takes a single argument:
=MONTH(serial_number)The function returns a single number between 1 and 12, representing the month itself. The months start at 1 for January and continue through to 12 for December.
Let’s walk through a step-by-step example of how to get the month number in Excel using the MONTH() function.
Step 1: Load Your Dataset in Excel
In this example, we’ll use a dataset that contains seven days. The dates come from a variety of years, just to demonstrate how it works.
Step 2: Use the MONTH() Function to Get the Month Number
By passing a date into the MONTH() function, Excel will return a number between 1 and 12. The months, logically, start at 1 for January and go all the way through to 12 for December. Because of this, the function will always return a positive integer.
Step 3: Use the Fill Handle to Copy the Formula Down
By clicking and dragging the fill handle, you can easily copy and paste the formula for multiple cells. When you drag the formula down for the entire range of values, Excel will extract the month number from each of the cells in the dataset.
In the following section, you’ll learn how to use the Excel formatting to display a month name for a date without losing the details of the date.
How to Use Excel Formatting to Display a Month for a Date
In the previous sections, we used Excel to return either the month name or the month number. In both of these cases, the detail of the date isn’t carried forward, meaning that we either get a string of text or a number.
In some cases, however, you’ll want to simply display the month name, rather than overwriting the details of the date itself. In these cases, you can make use of the formatting options available in Excel.
To see how this works, let’s walk through a step-by-step example.
Step 1: Load Your Dataset in Excel
In this example, we’ll use a dataset that contains seven days. The dates come from a variety of years, just to demonstrate how it works.
Step 2: Select the Formatting Option Menu
With the cells selected, click on the dropdown on the Number formatting options. This will open up the Format Cells window.
Step 3: Select Custom and Click OK
Select the Custom category and type mmm into the Type field. This will format the date as an abbreviated month name. If you want to use a long form, instead, you can also pass in mmmm. Select OK and Excel will apply the formatting to the cells.
Conclusion
In this tutorial, you learned how to get the month from a date in Excel. First, you learned how to get the month name from a date in Excel using the TEXT() function. The function allows you to get both a short-form month name, such as Jan or Feb. Similarly, the function allows you to get a long-form name, such as January and February. From there, you learned how to get the month number from a date in Excel using the MONTH() function. Finally, you learned how to simply display the name of a month without changing the underlying date value using the formatting options.
Additional Resources
To learn more about related topics, check out the tutorials below: