YEARS

Returns the number of years between two dates.

Syntax:

YEARS(startdate, enddate, mode)

If mode is 0, YEARS returns the number of whole years between startdate and enddate, day/month to day/month.

If mode is 1, YEARS identifies the year that startdate and enddate each lie in, and returns the difference between those years. In other words it returns YEAR(enddate) - YEAR(startdate)

If startdate is after enddate the result will be negative.

Example:

YEARS("2009-04-03", "2011-11-17", 0)

returns 2.

YEARS("2011-02-28", "2012-02-28", 0)

returns 1. 2012 is a leap year, but 28th February to 28th February is classed as a whole year.

YEARS("2012-02-29", "2013-02-28", 0)

returns 0. 2012 is a leap year, but 29th February to 28th February is not classed as a whole year, even though both dates are the last day of February.

YEARS("2009-04-03", "2011-11-17", 1)

returns 2.

YEARS("2009-12-31", "2010-01-01", 1)

returns 1. Even though there is only one day between the dates, they lie in different years.

Application:

Here is an application of using a hypothetical employee database. Let's say a company needs to find the number of full years each employee has worked to determine who is eligible for a long-service award, which is given after 10 full years of employment.

Employee Data Table:

	Employee ID	Name	Start Date	End Date	Years of Service	Eligible for Award? (Years >= 10)
	A	B	C	D	E	F
1	101	John Smith	3/15/2005	8/8/2025	20	Yes
2	102	Jane Doe	6/20/2018	8/8/2025	7	No
3	103	Peter Jones	11/1/2010	8/8/2025	15	Yes
4	104	Mary Williams	1/10/2024	8/8/2025	1	No

John Smith: YEARS(2005-03-15, 2025-08-08) returns 20, as he has completed 20 full years of service.
Jane Doe: YEARS(2018-06-20, 2025-08-08) returns 7, as she has completed 7 full years. The 8th year is not yet complete.
Peter Jones: YEARS(2010-11-01, 2025-08-08) returns 14, as he has completed 14 full years.
Mary Williams: YEARS(2024-01-10, 2025-08-08) returns 1, as she has completed 1 full year.

The "Eligible for Award?" column is a result of a conditional check, not a direct calculation from the YEARS function itself. It's a binary outcome (Yes/No) based on whether an employee meets the eligibility criteria for the long-service award.

Here's a breakdown of the results in that column, row by row:

John Smith: The YEARS function calculated his years of service as 20. The eligibility rule is that an employee must have at least 10 years of service (Years of Service >= 10). Since 20 is greater than 10, the condition is true. Therefore, the result in the "Eligible for Award?" column is "Yes".
Jane Doe: The YEARS function calculated her years of service as 7. The eligibility rule is Years of Service >= 10. Since 7 is not greater than or equal to 10, the condition is false. Therefore, the result is "No".
Peter Jones: The YEARS function calculated his years of service as 14. The eligibility rule is Years of Service >= 10. Since 14 is greater than 10, the condition is true. Therefore, the result is "Yes".
Mary Williams: The YEARS function calculated her years of service as 1. The eligibility rule is Years of Service >= 10. Since 1 is not greater than or equal to 10, the condition is false. Therefore, the result is "No".