The YEARFRAC function is a date and time function which returns the percentage of a year which has passed between two dates, calculations can be done one of five ways, which you can select in the formula builder.

This is the format of the YEARFRAC function:

=YEARFRAC(START DATE, END DATE, CALCULATION TYPE)

Here is an example of the YEARFRAC function:

=YEARFRAC(01-01-2020, 01-01-2022, 1) = 2

YEARFRAC function returning the proportion of years which have passed between the selected start and end dates

This function is valuable when you need to calculate things such as years of service for employees, subscription periods for clients, or to track repayment schedules. It is a very specific function whose use has often been mitigated by things like the DAYS function, even when it is the more accurate of the two.

An example of a functional implementation of this would be to determine the eligibility of your customers for discounts or other benefits based on their history with you. Clients who have hit their one year anniversary of purchasing from you are eligible for a light commemorative discount, those who have hit 2-4 years with you are eligible for a larger discount, and those over five years receive a large discount.

ex: date of first purchase is in column D, target date for the promotion is in cell G1, and you are entering their eligibility into column G

=IF(YEARFRAC(D2,$G$1)<1,””, IF(YEARFRAC(D2,$G$1)<2,”10% DISCOUNT”, IF(YEARFRAC(D2,$G$1)<5,”20% DISCOUNT”,”30% DISCOUNT”)))

* Note that nesting IF functions can become confusing and difficult to troubleshoot, read up on some tips and tricks for nesting IF functions! *