Calculating someone’s age or an anniversary (or, more generally, the number of years between two events) is a common mental task, as well as a spreadsheet task.
1st Approximation
As a quick first approximation (often performed mentally), we merely subtract the beginning year from the ending year:
= YEAR(EndDate) – YEAR(BegDate)
This math is easy, but it is often inaccurate with an error of 1 year. Consider a baby born in the middle of November. By Christmas, the simple formula says the baby is 0 years-old. That’s fine. But come New Year’s Day (and for months beyond), the formula says the baby is suddenly 1 year-old. Fortunately, the same 1-year error is hardly noticeable when we are looking for a ballpark estimate of a friend’s age or the time between historical events.
2nd Approximation
To be more accurate, let’s compute the number of days between the two events and divide by the average length of a year, 365.25 days, and then get the integer value:
= INT((EndDate – BegDate)/365.25)
This comparatively simple formula works very well for most common uses in Excel. However, when accuracy is a mandatory requirement (Ex: warranty expiration or contract law), this can fail in the immediate vicinity of the month and day of the anniversary due to the exact position of nearby Leap Years.
3rd Approximation
Even using Excel’s function YEARFRAC(BegDate, EndDate, Basis) is not a perfect solution:
=INT(YEARFRAC(BegDate,EndDate,1)
Depending on the 3rd parameter (Basis), it either flubs results for birthdays occurring on the 31st or for a Leap Day baby on another February 29th .
4th Approximation (PERFECTION!)
The answer to all these difficulties is fairly simple: Let’s begin by subtracting the years (as in the 1st Approximation) and then, if necessary, performing an error correction by subtracting 1 year:
=YEAR(X2)-YEAR(X1)+IF(DATE(2000,MONTH(X2),DAY(X2))<DATE(2000,MONTH(X1),DAY(X1)),-1,0)
where X1 = BegDate
. X2 = EndDate
The necessity of subtracting 1 occurs when the EndDate has not yet rolled around to the same month and day as the BegDate. The Leap Year 2000 is chosen as the reference year because it has a February 29th. Conceptually, there is a February 29th in every year. However, for 3 years, it only lasts for less than a nanosecond.