The answers given by chuff and teylyn work fine for scientific calculations; e.g., the normal height and weight of a person of a given age, or how long a bottle of wine has aged. This may be good enough for you. But they’re not perfect for legal purposes; e.g., on 5/31/2013, their formula computed 16.99931554. Admittedly, this will display as 17.00
if displaying two decimal digits, but it will test as being less than 17. Not good if you are trying to compute somebody’s age in the normal, societal/legal sense, that we would use to determine, for example, whether he’s old enough to buy that bottle of wine.
For brevity, I’ll use the alternative suggestion that the ancient date is in A1
, and that today’s date is in T1
. I suggest that you use this formula:
=(YEAR(T1)-YEAR(A1)) + (DATE(YEAR(A1),MONTH(T1),DAY(T1))-A1)/365.25
This computes the difference between the two years, and then computes the fractional year difference between the start date and the current date mirrored in the ancient year. For example,
I’m writing this on June 12, 2013. The first part of the above formula, YEAR(T1)-YEAR(A1)
(the surrounding parentheses aren’t necessary) is 2013-1996
which, of course, is exactly 17
.
The second term,
(DATE(YEAR(A1),MONTH(T1),DAY(T1))-A1)/365.25
becomes (DATE(1996,6,12)-A1)/365.25
or ("6/12/1996"-"5/31/1996")/365.25
or 12/365.25
,
which is 0.03012
, so the sum is 17.03012
.
But if T1
is 5/31/2013
, the formula evaluates to 17.00000
.
Oh, my formula has a slight glitch. If the ancient date is in a non-leap-year (e.g., 5/31/1995), and the current year is a leap year (e.g., 2012), then T1
= 2/29/2012
and T1
= 3/1/2012
will yield the same result, because, even though Excel raises an error for 2/29/1995
, it treats DATE(1995,2,29)
as 3/1/1995
.
4Excel can’t tell from the context (an arithmetic expression) that you’re talking about a date, so it interprets/ evaluates “5/31/1996” as (5/31)/1996 = 5/(31×1996), which is a very small value (8 E-05) –– so your expression collapses to approximately
TODAY()/365.25
. This evaluates to (current date) - (epoch date), in years; i.e., 2013-1900, plus 0.41, since we’re almost halfway through 2013. See chuff’s answer for how to fix it. – Scott – 2013-05-31T00:02:18.793