YEARFRAC

Returns the number of years including fraction between two dates.

Syntax:

YEARFRAC(startdate; enddate; basis)

returns the number of years between the startdate and enddate; basis is an integer specifying the system of how many days are in a month or year, as follows:

basis	day counting system
0	US (NASD) system. 30 days/month, 360 days/year (30/360)
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

Example:

YEARFRAC("2007-01-01";"2009-07-01";0)

returns 2.5.

YEARFRAC("2008-01-01";"2008-07-01";3)

returns 0.498630136986.

Application:

Here is a real-world example 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".