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

Dates in Microsoft Excel are useful for tracking finances, orders, and sales. So, there may come a time when you want to pull a month or year out of a date entry. 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 if you want to display the result as a number, word, or abbreviation. And luckily, you can use these same methods to get the year from your date entry.

Get the Month or Year With Date Functions

If you want to get the numeric value for a month such as 10 for October, 11 for November, and so on, the MONTH function gets the job done quickly. For years, you can simply 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) and YEAR(reference) where you refer to the cell containing the date.

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


You’ll then see the result in the cell containing the formula.

YEAR function to obtain the year from a date

Remember, the month is formatted as its numeric value.

MONTH function to obtain the month from a date

If you have a list of dates where you want to grab the month and/or year for each entry, use the fill handle to drag the formula(s) down to the remaining cells.

Copy formula to cells using the fill handle

Get the Month or Year With the TEXT Function

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

RELATED: How to Find the Day of the Week From a Date in Microsoft Excel

The syntax for the function is TEXT(value, format_code) where you’ll need both arguments to display the month. The value is the cell containing the date and the format_code is how you want to display the result.

Here, we’ll get the month for the date in cell A2 as a full word using the letter M for month as the format_code:


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

Full month name using the TEXT function

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


Abbreviation for month name using the TEXT function

You can also use the TEXT function if you only want two digits for the year rather than all four. You’ll use the letter Y for year as the format_code:


Two digits for the year using the TEXT function

Again, you can use the fill handle to copy the formula(s) down to your remaining cells if you like.

Copied formulas to cells using the fill handle

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

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »