# How to calculate age in years?

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.

-
Excel 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 May 31 '13 at 0:02

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
``````
-
Aha! The trick is the quotes. The `+0` is unnecessary; `=(TODAY()-"5/31/1996") / 365.25` works just fine. (Oh, BTW: chuff already gave the `=(TODAY()-A1)/365.25` answer.) – Scott May 31 '13 at 23:26

"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.

-
Handy function! Not in the standard list, presumably because it's there for Lotus 1-2-3 compatibility. I wonder what else is lurking under the hood like that? – Tom Zych Nov 11 '15 at 14:13

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

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

-

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`.

-