YEAR
Extracts the year from a date.
=YEAR(serial_number)This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Extracts the year from a date.
The YEAR function extracts the year component from a valid Excel date, returning a four-digit integer between 1900 and 9999. Because Excel stores dates as sequential serial numbers—where January 1, 1900, is represented by the number 1—the function parses these values to identify the corresponding calendar year. Best practices include referencing cells that contain date-formatted values rather than typing dates as text directly into the formula to avoid locale-specific errors. It is frequently used in financial modeling to group transactions or in human resources to calculate age or seniority by comparing the current year to a start date. Note that providing a cell with a serial number of 0 or a blank cell may result in a return value of 1900.
Quick reference
Syntax
=YEAR(serial_number)Inputs
Arguments
Example: A2
Formula patterns
Examples
Basic year extraction
=YEAR(A2)Calculate age from birth date
=YEAR(TODAY())-YEAR(A2)Identify dates in current year
=IF(YEAR(A2)=YEAR(TODAY()), "Current", "Prior")Avoid these issues
Common Errors
#VALUE!
Cause: The referenced cell contains a text string that Excel cannot interpret as a valid date, or the value is a non-numeric string.
Fix: Use the DATEVALUE function to convert text-based dates into serial numbers or ensure the cell is formatted as a Date.
#NUM!
Cause: The serial number provided is a negative number or is too large to be a valid Excel date (dates must be between 1/1/1900 and 12/31/9999).
Fix: Check for negative values or incorrect year inputs that fall outside the supported 1900-9999 range.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Extracts the year from a date.
umber: The date to extract from
#VALUE!: Use the DATEVALUE function to convert text-based dates into serial numbers or ensure the cell is formatted as a Date. #NUM!: Check for negative values or incorrect year inputs that fall outside the supported 1900-9999 range.