Inconsistency with YEARFRAC Function

Hello,

I am having some difficulty with the YEARFRAC function not working consistently. If I have 3 dates (date1,date2,date3) and wish to find the exact number of years between date1 and date3, the formula is YEARFRAC(date1,date3,1). Now, it seemed reasonable to me that if I first found the number of years between date1 and date2 and then found the number of years between date2 and date3, I would be able to add these values together to get the number of years between date1 and date3.

However, it appears that there is some slight variation in the calculation because YEARFRAC(date1,date2,1) + YEARFRAC(date2,date3,1) will not necessarily equal YEARFRAC(date1,date3,1). The difference will not be very large, but it is noticeable.

Is anyone familiar with why this occurs and a possible way to fix it?

When you use YEARFRAC with a 3rd argument of 1, i.e.

=YEARFRAC(date1,date2,1)

then the actual calculation done is

=(date2-date1)/days_in year

Unfortunately days_in year is sometimes 365 and sometimes 366 based on the actual dates, so it's possible that the first calculation (date 1 to date 2) uses 365, the second (date2 to date 3) uses 366 and then date 1 to date 3 uses 365 again. This is what causes the discrepancey

If you just use the formula

=(date2-date1)/365

or YEARFRAC with a third argument of 3, i.e.

=YEARFRAC(date1,date2,3)

then you should get consistent results