Get year from date

To extract the year from a date, you can use the YEAR function. In the generic form of the formula above, the date must be in a form that Excel recognizes as a valid date. In the worksheet shown, the formula in cell D5 is:

=YEAR(B5)

As the formula is copied down, YEAR extracts a 4-digit year from each date in column B.

Explanation

In this example, the goal is to extract the year number from a list of dates in column B. This can be easily achieved with the YEAR function.

The YEAR function takes just one argument, the date from which you want to extract the year. For example, in the formula below, we pass the "12-Dec-1999" into the YEAR function, which returns 1999:

=YEAR("12-Dec-1999") // returns 1999

In the worksheet shown, we use a cell reference instead of hard-coding the date. The formula in cell D5 is:

=YEAR(B5)

The result in D5 is 1912 since the date in B5 is 4-Apr-1912. As the formula is copied down, it returns a four-digit year for each date listed in column B.

Dates as text

Note that you can use YEAR to extract the year from a day entered as text:

=YEAR("1/5/2016") // returns 2016

However, using text for dates can cause unpredictable results on computers using different regional date settings. In general, a much better approach is to provide a cell reference that already contains a valid date. If YEAR returns a #VALUE error, it means Excel does not recognize the value as a date. For some ways to get Excel to recognize dates, see: Convert text to date.

Display year only

In some cases, you may want to enter a date and only display the year. You can accomplish this by applying a custom number format like "yyyy" or "yy" to one or more dates.