How to get the month or year of a date in Microsoft Excel

A calendar next to several colored thumbtacks on a purple surface.
Chutima Chaochaiya/Shutterstock.com

Dates in Microsoft Excel are useful for keeping track of finances, orders, and sales. So there may come a time when you want to extract a month or a year from a date input. The functions and formulas make this an easy task.

There are a couple of ways to get a month from a date in Excel, depending on whether you want to display the result as a number, a word, or an abbreviation. And luckily, you can use these same methods to get the year from your date input.

Get the month or year with date functions

If you want to get the numerical value of a month, like 10 for October, 11 for November, etc., the MONTH function gets the job done quickly. For years, you can just use the YEAR function and a reference.

RELATED: How to remove a year from a date in Microsoft Excel

The syntax for each function is the same: MONTH(reference) Y YEAR(reference) where refers to the cell that contains the date.

You can use the following formulas to get the month and then the year from the date in cell A2:

=MONTH(A2)
=YEAR(A2)

You will then see the result in the cell containing the formula.

YEAR function to get the year from a date

Remember, the month is formatted as its numeric value.

MONTH function to get the month of a date

If you have a list of dates where you want to take the month or year for each entry, use the fill handle to drag the formulas down to the remaining cells.

Copy formula to cells using fill handler

Get the month or year with the TEXT function

Perhaps you prefer to see the name of the month or an abbreviation instead of the number. You can do this for the month using the TEXT function. You can also get the two-digit number of the year with this method.

RELATED: How to find the day of the week from a date in Microsoft Excel

The syntax of the function is TEXT(value, format_code) where you will need both arguments to display the month. the value is the cell that contains the date and the format_code is how you want to display the result.

Here, we will get the month for the date in cell A2 as a whole word using the letter M for the month as the format_code:

=TEXT(A2,"mmmm")

Note that you need at least four M’s in quotes to get the full month name. The number of letters in the month name does not correspond to the number of M’s in the argument; just enter at least four of them.

Full name of the month using the TEXT function

To get the three-letter abbreviation of a month instead of the full name, just use three M’s:

=TEXT(A3,"mmm")

Abbreviation of the name of the month using the TEXT function

You can also use the TEXT function if you only want two digits for the year instead of all four. You will use the letter Y for the year as the format_code:

=TEXT(A2,"yy")

Two digits for the year using the TEXT function

Again, you can use the fill handle to copy the formulas to the remaining cells if you like.

Formulas copied to cells using the fill handle

Taking a month or a year out of an entire date in Excel takes only a minute with these methods. For more information, check out these additional date and time functions in Excel that you might find useful.

Leave a Comment