Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
3  
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
add comment

4 Answers

up vote 3 down vote accepted

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
share|improve this answer
    
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
add comment

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

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

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.