Excel - Calculating age of death

Asked By Anni on 21-Feb-08 09:19 AM
Hi
I work in a hospital and have to calculate what exact age someone is when
they die.  I have birth date and date of death.  How can I calculate in total
the age on death.  Example,   45 years, 4 months and 5 days

Many thanks


--
Kind regards

Ann Shaw




Mike replied on 21-Feb-08 09:30 AM
Maybe

=DATEDIF(A1,A2,"y")&" y "&DATEDIF(A1,A2,"ym")&" m "&DATEDIF(A1,A2,"md")&" d"

Where a1= DOB
a2 = DOD

Mike H
Ron Rosenfeld replied on 21-Feb-08 09:50 AM
On Thu, 21 Feb 2008 06:30:02 -0800, Mike H <MikeH@>


Since this is in a hospital, and the result probably going on some kind of
legal document, I think you need to have the legal definition of "age" for this
purpose.

Some odd results arise with that formula when it is used for this kind of
determination:

DOB:	31 Jan 1943
DOD:	01 Mar 2008

Your Formula:  65 y 1 m -1 d


--ron
Rick Rothstein \(MVP - VB\) replied on 21-Feb-08 12:02 PM
I've always thought measuring a time span using years, months and days is
somewhat useless as the months part is not a very definitive increment. The
number of days spanned by some number of months differs depending on the
months being spanned. Hell, even years can be somewhat problematic give the
occurrence of leap years within time spans; but, when used by itself as a
by specifying a time span in years, months and days has always bothered me
(way more so than simply specifying years and days, even though I recognize
the inaccuracy introduced by the leap years here).

Rick
Niek Otten replied on 21-Feb-08 12:49 PM
Indeed. Financial (and actuarial, my area of interest) systems often use this. But because product specifications often *do* refer
to months, the 360-day system is somewhat popular in those groups. Not that it's perfect!
It assumes a 360-day year, consisting of 12 30-day months.
As you can imagine, the remaining 5 or 6 days are subject to lots of different interpretations, but AFAIK they boil down to 2
systems; NASD or European (see HELP for DAYS360).

If only customers would specify what "number of months difference" means (to them).....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Ron Rosenfeld replied on 21-Feb-08 12:56 PM
On Thu, 21 Feb 2008 12:02:14 -0500, "Rick Rothstein \(MVP - VB\)"


In general I agree with you, but there are certain legal ramifications in
certain areas, and, if clarified, can allow one to express a time span even
with the inclusion of "months".

For example, some kinds of aviation certifications are defined in terms of
something that can be computed (easily) using DATEDIF.
--ron
Zanny Garbett replied on 09-Jan-09 01:35 PM
Try this with:

[A1] = date of birth;
[B1] = Date of death;

=DATEDIF(A1,B1,"y")&"y,"&DATEDIF(A1,B1,"ym")&"m,"&DATEDIF(A1,B1,"md")&"d"

Should work OK

Regards
Zanny
Susie Toews replied to Anni on 27-Feb-11 06:48 PM
=YEAR(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1900&" Years "&IF(MONTH(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1=0,"",MONTH(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1&" Months ")&IF(DAY(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1=0,"",DAY(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1&" Days")
Susie Toews replied to Anni on 27-Feb-11 06:50 PM
=YEAR(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1900&" Years "&IF(MONTH(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1=0,"",MONTH(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1&" Months ")&IF(DAY(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1=0,"",DAY(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1&" Days")



Deathdate= D8

Birthdate= B8
Konczér, Tamás replied to Susie Toews on 03-Mar-11 05:08 AM
Hi Susie,

Since I need the same kind of calculation gave a try the above listed
formula. Unfortunately it is not correct, either Excel 2003 and 2007
ask for fixing.

Regards,
Tamas

1900&" Years "&IF(MONTH(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,DAY(D8=
)-DAY(B8)+1))-1=3D0,"",MONTH(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)+1,D=
AY(D8)-DAY(B8)+1))-1&" Months ")&IF(DAY(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MO=
NTH(B8)+1,DAY(D8)-DAY(B8)+1))-1=3D0,"",DAY(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)=
-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1&" Days")
en
e in total
d")&" d"
nd of
d of
ys is
nt. The
the
ive the
as a
implied
red me
cognize
n use this. But because product specifications often *do* refer
Not that it is perfect!
f different interpretations, but AFAIK they boil down to 2
means (to them).....
ions in
span even
terms of
tion), but not
,"md")&"d"
(B8)+1))-1900&" Years "&IF(MONTH(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONTH(B8)=
+1,DAY(D8)-DAY(B8)+1))-1=3D0,"",MONTH(DATE(YEAR(D8)-YEAR(B8),MONTH(D8)-MONT=
H(B8)+1,DAY(D8)-DAY(B8)+1))-1&" Months ")&IF(DAY(DATE(YEAR(D8)-YEAR(B8),MON=
TH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1=3D0,"",DAY(DATE(YEAR(D8)-YEAR(B8),=
MONTH(D8)-MONTH(B8)+1,DAY(D8)-DAY(B8)+1))-1&" Days")
s
7f-e...
joeu2004 replied to Konczér, Tamás on 03-Mar-11 07:46 AM
If B8 is the birth date and D8 is the date of death, then ostensibly
do:

=3DDATEDIF(B8,D8,"y")

Caveat:  DATEDIF does not handle Feb 29 "correctly", where "correctly"
means:  consistent with EDATE.  If B8 is 2/29/1948 and D8 is
2/28/2011, most people would consider the 63 years, not 62.

If you that is what you want, too, then do:

=3DDATEDIF(B8,D8,"y")
+(D8=3DEDATE(B8,12+12*DATEDIF(B8,D8,"y")))

Note:  If you get a #NAME error in XL2003, you need to install the
ATP.  If you cannot or do not want to install the ATP, an alternative
is possible, but it would be best to use a helper cell in that case.
Let us know if you need the alternative.
Ron Rosenfeld replied to Susie Toews on 03-Mar-11 08:07 AM
Depending on the definition of age, this also may be giving an erroneous result.

For example,
DOB:	31 Jan 1943
DOD:	  1 Mar 2008


I would have this person age at death as  65 years 1 month 1 day; your formula returns 65 years 29 days.
Ron Rosenfeld replied to joeu2004 on 03-Mar-11 11:21 AM
Since th OP also wanted months and days, Datedif may be inappropriate.

Try:

A1:	31 Dec 2010
A2	4   Jan 2011


=DATEDIF(A1,A2,"md")

In XL2007, --> 117 .  Should be 4
joeu2004 replied to Ron Rosenfeld on 03-Mar-11 12:43 PM
[....]

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.
Gord Dibben replied to Ron Rosenfeld on 03-Mar-11 01:34 PM
Ron

I get 4 with either 2003 and 2007


Gord
Ron Rosenfeld replied to Gord Dibben on 03-Mar-11 03:47 PM
Gord,

Which SP do you have for 2007?

The function apparently broke in one of the SP's for 2007, but I cannot recall if it was SP1 or SP2.  I have SP2 and it definitely is broken.
joeu2004 replied to Ron Rosenfeld on 03-Mar-11 03:54 PM
According to Rick Rothstein, MVP:  "The problem did not come about with
Excel 2007, rather, it came about with Service Pack 2 for Excel 2007".
Ron Rosenfeld replied to joeu2004 on 03-Mar-11 04:27 PM
Avoiding VBA, and also avoiding DateDif since it is unsupported, and who knows what might break in the future, I would be inclined to use helper columns:

C2 (Years):	=YEAR(EndDt)-YEAR(StartDt)-(EndDt<DATE(YEAR(EndDt),MONTH(StartDt),DAY(StartDt)))
D2 (Months): 	=MATCH(TRUE,EDATE(StartDt,C2*12+ROW(INDIRECT("1:12")))>EndDt,0)-1   ***array-entered***
E2 (Days):	=EndDt-EDATE(StartDt,C2*12+D2)

And then put it together into the desired string format.

But I prefer a UDF to output my desired string:

=============================
Option Explicit
Function DateIntvl(d1 As Date, d2 As Date) As String
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long
Dim sOutput() As String

yr = Year(d2) - Year(d1) + (d2 < DateSerial(Year(d2), Month(d1), Day(d1)))

i = 0
Do Until DateAdd("m", yr * 12 + i, d1) > d2
i = i + 1
Loop
mnth = i - 1

dy = d2 - DateAdd("m", yr * 12 + mnth, d1)

Select Case (yr > 0) + (mnth > 0) + (dy > 0)
Case Is = 0 Or -1
ReDim sOutput(0)
Case Is = -2
ReDim sOutput(0 To 1)
Case Is = -3
ReDim sOutput(0 To 2)
End Select
If yr > 0 Then sOutput(0) = yr & IIf(yr = 1, " year", " years")
If mnth > 0 Then sOutput(0 - (yr > 0)) = mnth & IIf(mnth = 1, " month", " months")
If dy > 0 Or (yr = 0 And mnth = 0) Then sOutput(0 - (yr > 0) - (mnth > 0)) = dy & IIf(dy = 1, " day", " days")

DateIntvl = Join(sOutput, ", ")

End Function
================================
joeu2004 replied to Ron Rosenfeld on 03-Mar-11 05:54 PM
[....]

Your function returns "12 months" instead of "1 year" for the dates
2/29/1948 and 2/29/1949 (d1 and d2).

Without debugging your mistake, I think the implementation of the
output construction can be greatly simplfied, IMHO.  See the revision
at the end below.

I was going to suggest using VBA DateDiff since that __is__ documented
in VBA Help and presumably supported.

But it has some odd quirks, one of which is documented, to wit:  "When
comparing December 31 to January 1 of the immediately succeeding year,
DateDiff for Year ("yyyy") returns 1 even though only a day has
elapsed".

Actually, the same "round up" error arises when the start when the
interval is "m" (months).

I did not bother to vet your Excel implementation.

But an errata of my own....  I made a mistake in a last-minute
misguided "simplification" of the last subexpression (days).  I should
have written:

=3DDATEDIF(A4,B4,"y")
+(B4=3DEDATE(A4,12+12*DATEDIF(A4,B4,"y")))
& " years, "
& MOD(DATEDIF(A4,B4,"m")
+(B4=3DEDATE(A4,1+DATEDIF(A4,B4,"m"))),12)
& " months, "
& B4-EDATE(A4,DATEDIF(A4,B4,"m")
+(B4=3DEDATE(A4,1+DATEDIF(A4,B4,"m"))))
& " days"

-----

Simplified DateIntvl:

Option Explicit

Function DateIntvl(d1 As Date, d2 As Date) As String
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long
Dim s As String

'NOTE:  It would be prudent for the type of DateIntvl
'to be Variant and return an appropriate CVErr() if
'd1>=3Dd2

yr =3D Year(d2) - Year(d1) + (d2 < DateSerial(Year(d2), Month(d1),
Day(d1)))

i =3D 0
Do Until DateAdd("m", yr * 12 + i, d1) > d2
i =3D i + 1
Loop
mnth =3D i - 1

dy =3D d2 - DateAdd("m", yr * 12 + mnth, d1)

If yr > 0 Then s =3D ", " & yr & IIf(yr =3D 1, " year", " years")
If mnth > 0 Then _
s =3D s & ", " & mnth & IIf(mnth =3D 1, " month", " months")
If dy > 0 Or (yr =3D 0 And mnth =3D 0) Then _
s =3D s & ", " & dy & IIf(dy =3D 1, " day", " days")
DateIntvl2 =3D Mid(s, 3)

End Function

NOTE:  "Or (yr =3D 0 And mnth =3D 0)" becomes unnecessary if you add the
d1>=3Dd2 suggested error checking.
joeu2004 replied to joeu2004 on 03-Mar-11 06:00 PM
Obvious typo:  2/29/1948 and 2/28/1949.

Sigh, I have really gotten used to the Edit feature in the Answers
Forum ;-).
Ron Rosenfeld replied to joeu2004 on 03-Mar-11 10:26 PM
I assume you mean 2/28/1949 for d2.

But how to handle that situation is not entirely clear, and also gets into legalities.  The most common instance has to do with when a leapling celebrates their birthday.  But more important might be when a leapling is legally considered to have attained a certain age, e.g. that of legal majority.  In the US I have read, but not been able to definitively document, that "most" states consider the leapling to have his/her birthday on Mar 1 of the common year.  I have read that in England and Taiwan the leapling is considered to have his/her birthday on Feb 28 during common years.

I think, for now, I will leave it at 12 months, and add a comment in the UDF that indicates it is US-centric.

I started using IIF's and even nested IIF's and found it easier to set up the array and let the Join function handle the <comma><space> delimiter.


I avoided DateDiff because of the same uncertainty as how bulletproof it is.


It uses similar logic to the UDF, but your example of 29Feb --> 28Feb causes the month calculation to error; that is easily fixed by adding another month to the array:

=MATCH(TRUE,EDATE(StartDt,C2*12+ROW(INDIRECT("1:13")))>EndDt,0)-1


Good suggestion
joeu2004 replied to Ron Rosenfeld on 04-Mar-11 12:39 AM
Good "assumption".  I said as much in my follow-up errata that I
posted nearly 4.5 hours before your response.


Non sequitur.  My point was:  your VBA function outputs "12 months",
and I believe 12 months is synonymous with "1 year" in anyone's book.

(Well, anyone that follows the so-called Western calendar. ;->)


I have never heard or read of that in the US.  On the contrary....

As the wiki page that you read states:  "English law of 1256 decreed
that in leap years, the leap day and the day before [...] are to be
reckoned as one day for the purpose of calculating when a full year
had passed. In England and Wales a person born on February 29 legally
reaches the age of 18 or 21 on February 28 of the relevant year".

English law before the US independence is called Common Law in the
US.  And Common Law, especially civil common law, is generally
followed in the US unless there is statutory or constitutional law
(including written case law) to the contrary.

For example, Calif Civil Code section 22.2 says as much directly.

(But I have not researched Calif law to see if it states anything
different for the anniversary of Feb 29 per se.)

The website http://www.leapyearday.com/driverslicenses.htm has several
anecdotal stories where Feb 28 was recognized as the anniversary of a
Feb 29 birth date by states.  One writer states:  "the Texas
Department of Public Safety has since changed the format
of the driver's license [...].  When my expiration date is not in a
Leap Year, it shows as expiring on February 28th".

I believe that US federal law uses Feb 28 as the anniversary of Feb 29
where applicable.  But I would have to do a "full-court press" to do
the legal research properly.

As suggestive, albeit not dispositive evidence, note that the Truth In
Lending Act, Appendix J states:  "If a series of payments (or
advances) is scheduled for the last day of each month, months shall be
measured from the last day of the given month to the last day of
another month. If payments (or advances) are scheduled for the 29th or
30th of each month, the last day of February shall be used when
applicable".

(But even if I could find dispositive of federal law, I am not saying
that is binding on the states in areas that are not controlled by
federal law.)

But really, this issue is neither here nor there.

I had simply noted at the outset that __my__ goal was to be consistent
with EDATE, which does treat Feb 28 as the anniversary of Feb 29.  And
following.

You do not have to agree with that goal.  But then it seems odd that
you rely on EDATE in your Excel formulation.

And it seems odd that your VBA implementation returns 12 months.  If
your position is that Feb 28 is not 1 year after Feb 29, then I would
expect your result to be 11 months 30 days, since



If you want to use an array and Join, that is your prerogative.

But your justification does not wash.  You are using If...Then and
IIf() exactly as I use them.

You wrote ("reformatted to fit your screen"):
If yr > 0 Then _
sOutput(0) = yr & IIf(yr = 1, " year", " years")
If mnth > 0 Then _
sOutput(0 - (yr > 0)) _
= mnth & IIf(mnth = 1, " month", " months")
If dy > 0 Or (yr = 0 And mnth = 0) Then _
sOutput(0 - (yr > 0) - (mnth > 0)) _
= dy & IIf(dy = 1, " day", " days")

If yr > 0 Then _
s = ", " & yr & IIf(yr = 1, " year", " years")
If mnth > 0 Then _
s = s & ", " & mnth & IIf(mnth = 1, " month", " months")
If dy > 0 Or (yr = 0 And mnth = 0) Then _
s = s & ", " & dy & IIf(dy = 1, " day", " days")

There is nothing "easier" about your implementation with respect to
IIf() and If...Then per se.  Both of our implementations are identical
in that respect.

The only difference is that I append to string variable "s", then clip
the first 2 characters (always ", "), whereas you allocate (Redim)
array "sOutput0" and use Join to concatenate the sOutput0 components
with a ", " separator.

Obviously, which is "simpler" is subjective.  That is why I wrote
Gord Dibben replied to Ron Rosenfeld on 04-Mar-11 11:55 AM
I am running SP2...............not broke<g>

Don't know if I ever installed SP1.


Gord
Rick Rothstein replied to Gord Dibben on 04-Mar-11 01:21 PM
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)
Rick Rothstein replied to joeu2004 on 04-Mar-11 01:28 PM
Below my signature is the message I posted to the newsgroups back when this
problem first surfaced.

Rick Rothstein (MVP - Excel)

From a previous newsgroup posting of mine???

You might want to reconsider using the DATEDIF function. It is an
undocumented (and, thus, probably an unsupported) Excel function which
appears to be broken in XL2007 at Service Pack 2. Someone recently posted
this message as part of a newsgroup question???

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

In 2007, this gives me 122. This happens all the way up to the point
where the second date is 1/26/2012 and then it hits zero at 1/27/2012.
In 2002, however, it gives me the correct answer of 9.
**********************************************************************

An informal survey of fellow MVPs shows the above formula works correctly in
the initial release of XL2007 and its SP1, but does not work correctly in
SP2; hence, it appears to be broken at that level. The problem is that the
extent of the breakage is unknown (and probably indeterminable). In
addition, I would say, being an undocumented (and, thus, probably and
unsupported) function, the odds of Microsoft spending the time to search
down and fix whatever broke is slim. This would seem to mean that DATEDIF
cannot be counted on to work correctly from XL2007 SP2 onward. And even if
Microsoft did fix the problem in a subsequent Service Pack, any of your
users who remained at SP2 would be subjected to incorrect result.
Gord Dibben replied to Rick Rothstein on 04-Mar-11 02:10 PM
I also get 122


Gord
Rick Rothstein replied to Gord Dibben on 04-Mar-11 02:17 PM
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 will not get broken at SP3 and beyond),
especially given that DATEDIF is an undocumented function.

Rick Rothstein (MVP - Excel)
Konczér, Tamás replied to joeu2004 on 04-Mar-11 04:05 PM
Thank you. I was interested in the YMD like solution. (well,
erroneously I always write version 2007 however I got Off2010)