Guide on Isolating Month and Year Values From Date Entries in MS Excel Efficiently

Quick Links

• Get the Month or Year With Date Functions
• Get the Month or Year With the TEXT Function

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.

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:

=MONTH(A2)

=YEAR(A2)

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

Remember, the month is formatted as its numeric value.

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.

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:

=TEXT(A2,”mmmm”)

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.

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

=TEXT(A3,”mmm”)

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:

=TEXT(A2,”yy”)

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

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.

Also read: