How to calculate age in years?

Question

In Excel 2010, I'm using the formula =(TODAY() - 05/31/1996) / 365.25 to calculate the age in years between today's date, May 30, 2013, and May 31, 1996. I'm expecting some number close to 17.00, but I get 113.41. How do I use a single formula to calculate the difference in years between two dates? I've formatted my cell as a number with 2 decimal precision.

I'm using formula based on this similar question.

Answer 1

You need to coerce the date as written into a real date. There are several approaches. Using a DateValue() function has been outlined. Another way is this:

=(TODAY()-("31/5/1996"+0))/365.25

This formula works for regional settings with dates represented as DMY. If your regional settings use MDY, try

=(TODAY()-("5/31/1996"+0))/365.25

The text of the date will be coerced into a number by adding a zero, and will then be processed further.

Of course, you could save yourself a lot of trouble if you entered the date into a different cell and then just refer the formula to that cell

With A1 having the value of 31-May-1996 (in whatever format your regional settings serve up), you can then use

=(TODAY()-A1)/365.25

Answer 2

"How do I use a single formula to calculate the difference in years between two dates?"

Because of the exact way you have phrased your question I think the DATEDIF formula will better suit you. Try using the below formula. It's simple! Hope this helps. :)

=DATEDIF("05/31/1996",TODAY(),"Y") Or =DATEDIF(A1,TODAY(),"Y")

"Y" makes the formula count the difference in Years.

Answer 3

This will work: =(TODAY()-DATEVALUE("5/31/1996"))/365.25

As will this: =(TODAY()-A1)/365.25 where A1 contains an Excel datevalue.

Answer 4

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.