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.


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

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


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

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