Excel YEAR Function

What is the Excel YEAR Function?

The Excel YEAR function returns the year corresponding to date as a 4-digit number starting from 1900 until 9999. Use the YEAR function to extract the year from a date into a cell.

YEAR Syntax

YEAR(serial_number)

serial_number, required, a valid Excel date from which to extract the year.

Usage Note:

• Excel only accepts dates from January 1, 1900, to December 31, 9999, any date outside the range causes Excel to return a #VALUE error.

How to Use YEAR Function in Excel

Pay attention to the data in column A; there are seven dates with various display formats. Column B contains the YEAR function, extracts the year number from the date. The results are in accordance with the date entered except the last two dates. Why? Let’s look at one by one.

YEAR Function #1 – YEAR Function #5

There are many formats for displaying a date, but a valid excel date format must comply with the Windows regional settings on each computer.

To find out the valid Excel date format, look at the formula bar. The date in the second row until the sixth row has the same valid excel date format mm/dd/yyyy, even though the format of the display varies.

The result is the YEAR function #1 until the YEAR function #5 returns the correct numbers.

YEAR Function #6

The YEAR function on the seventh row returns a #VALUE! error, because a valid excel date format does not match the Windows regional settings format.

Please look at the formula bar; the date is a text. The display format is the same as the date on the second row, but the actual data is text. Note the quotation marks at the beginning; it is also a sign that the data on the seventh row is a text, not a date.

YEAR Function #7

Date in eight row matches to the valid excel date format; the problem is the date entered is less than January 1, 1900. Excel will consider it a text and returns the same error as the date in the previous row.

YEAR Example

Another Date/Time Function