Calculating age of death

  • Thread starter Thread starter Annie
  • Start date Start date
I also get 122


Gord

I get 4 as an answer also, however, those dates were not the ones I posted
to show the problem. Try this formula in one of your XL2007 cells...

=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

In my copy of XL2007 SP2, I get an answer of 122... it should be 9. What
answer do you get?

Rick Rothstein (MVP - Excel)
Click to expand...
 
I also get 122

Hence, DATEDIF is broken (at XL2007 SP2), at least for the "md" option,
although once broken, I am not so sure how safe it is to assume the other
options did not get broken as well (or won't get broken at SP3 and beyond),
especially given that DATEDIF is an undocumented function.

Rick Rothstein (MVP - Excel)
 
I would easily rule DATEDIF as more not reliable than it ever has been.

Good for quick and easy non-critical computations only.


Gord
 
=DATEDIF(B8,D8,"y")
+(D8=EDATE(B8,12+12*DATEDIF(B8,D8,"y")))
Click to expand...
[....]
Since th OP also wanted months and days, Datedif
may be inappropriate.
Click to expand...

Well, DATEDIF(...,"y") alone.  My bad:  I did not see the forest for
the trees.  I tend to look askance at responses to 3-year-old
questions in the first place.

But assuming that Tamas is interested in a year/month/day solution, I
would be inclined to use a helper cell, to wit:

X1:
=DATEDIF(B8,D8,"m")+(D8=EDATE(B8,1+DATEDIF(B8,D8,"m")))

Then the year/month/day string can be constructed using:

=INT(X1/12) & " years, "
& X1-12*INT(X1/12) & " months, "
& D8-EDATE(B8,X1) & " days"

But for those that like one-liners:

=DATEDIF(B8,D8,"y")
+(D8=EDATE(B8,12+12*DATEDIF(B8,D8,"y")))
& " years, "
& MOD(DATEDIF(B8,D8,"m")
+(D8=EDATE(B8,1+DATEDIF(B8,D8,"m"))),12)
& " months, "
& D8-EDATE(B8,DATEDIF(B8,D8,"m"))
-(D8=EDATE(B8,1+DATEDIF(B8,D8,"m")))
& " days"

PS:  I am not aware of any defects with DATEDIF(...,"y") and
DATEDIF(...,"m") other than its dubious handling of leap dates.  But I
know that some people advocate not using DATEDIF at all (at least
starting with XL2007 SP2) because of the defect with
DATEDIF(...,"md").  For consistency, they should also advocate not
using ROUND, INT and MOD, to name a few, because each has defects at
least in XL2003 and later.
Click to expand...

Thank you. I was interested in the YMD like solution. (well,
erroneously I always write version 2007 however I got Off2010)
 
Back
Top