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: