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
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
No comments:
Post a Comment