Infant Mortality

Question

I was asked to provide an Excel formula to calculate the age at death of an infant given the date of birth and the date of death.  The formula I supplied was:

=DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, " & DATEDIF(A2,B2,"md") & " days"

where the date of birth is in A2 and the date of death is in B2.  I demonstrated some examples like:

<br>2/2/2010	<br>3/4/2010	<br>0 years, 1 months, 2 days

 All the examples seemed reasonable

About an hour later, she returned with this result:

<br>12/2/2011	<br>1/1/2012	<br>0 years, 0 months, 143 days

and inquired why.

I told her I would get back to her.

What is the problem and is there a better formula??

Answer 1

DATEDIF is buggy in Excel 2007, see for example http://www.mrexcel.com/forum/showthread.php?t=302028. Apparently it has been corrected again in Excel 2010. I see that Ashish has already provided a workaround :)

Answer 2

Hi,

With the two dates as 2 December 2011 and 1 January 2012, the answer is get is 0, 0 and 30.  I wonder how she got the answer as 143?

Also, please note that the "md" bit of the DATEDIF() function is knowwn to be problematic - it does return negative number is some instances

With a starting date of 31 July 2007 and ending date of 2 March 2009, "md" in the DATEDIF() returns the answer as -1 (minus 1)

As an alternative to the "md" in the DATEDIF(), i use the following:

=B13-EDATE(A13,(C13*12)+D13)

where:

B13 is the ending date; and

A13 is the beginning date; and

C13 is the result of "y" in the DATEDIF(); and

D13 is the result of "ym" in the DATEDIF()

Answer 3

You are welcome.

Answer 4

Thanks!

Answer 5

Thanks!