The YEARFRAC function in Google Sheets is a powerful tool for calculating the number of years between two given dates. This function not only provides the whole years but also includes any fractional years based on the specified day count convention. It is particularly useful in financial analyses, allowing users to accurately evaluate periods and their impacts over time.

Syntax

YEARFRAC(start_date, end_date, [basis])

• start_date: The initial date from which the calculation begins.
• end_date: The final date up to which the calculation is performed.
• basis (optional): An integer that specifies the day count basis to use in the calculation. The default is 0 (US 30/360).

Example #1

YEARFRAC("2020-01-01", "2022-01-01")

This function calculates how many years are between January 1, 2020, and January 1, 2022, returning a result of 2.

Example #2

YEARFRAC("2020-01-01", "2022-06-01")

Here, the function computes the duration from January 1, 2020, to June 1, 2022, yielding a result of approximately 2.42 years.

Example #3

YEARFRAC("2020-01-01", "2022-01-01", 1)

This example uses a different day count basis. It calculates the years between January 1, 2020, and January 1, 2022, with a basis of ‘actual/actual’, resulting in a value of 2 years.

Error handling

• VALUE!: This error occurs when one or both of the date arguments are invalid.
• NUM!: This error appears if the basis argument is not a valid integer (0, 1, 2, 3, 4).
• N/A: Indicates that the function cannot compute the result if the start_date is later than the end_date.

Conclusion

In summary, the YEARFRAC function is invaluable for determining the exact number of years between two dates while accounting for the nuances of fractional years. Its flexibility with different day count conventions makes it a versatile tool for financial calculations and project timelines.