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.

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.