Calculating age of death

[Annie]

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

 

[Mike H]

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]

Maybe

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

Where a1= DOB
a2 = DOD

Mike H

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\)]

Maybe

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

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
"rough" indicator of time span, this if fine; however, the accuracy implied
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]

<way more so than simply specifying years and days,>

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


| >>Maybe
| >>
| >>=DATEDIF(A1,A2,"y")&" y "&DATEDIF(A1,A2,"ym")&" m "&DATEDIF(A1,A2,"md")&"
| >>d"
| >>
| >>Where a1= DOB
| >>a2 = DOD
| >
| > 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
|
| 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
| "rough" indicator of time span, this if fine; however, the accuracy implied
| 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
|

 

[Ron Rosenfeld]

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
"rough" indicator of time span, this if fine; however, the accuracy implied
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

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
"calendar months". The meaning is clear (if you know the definition), but not
something that can be computed (easily) using DATEDIF.
--ron

 

[Susie Toews]

=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")

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

On Thursday, February 21, 2008 9:30 AM Mike wrote:
Maybe

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

Where a1= DOB
a2 = DOD

Mike H

"Annie" wrote:

[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]

=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

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

On Thursday, February 21, 2008 9:30 AM Mike wrote:
Maybe

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

Where a1= DOB
a2 = DOD

Mike H

"Annie" wrote:

[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

 

[Konczér, Tamás]

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

=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

On Thursday, February 21, 2008 9:19 AM Anni wrote:
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

Ann Shaw

On Thursday, February 21, 2008 9:30 AM Mike wrote:
Maybe
=DATEDIF(A1,A2,"y")&" y "&DATEDIF(A1,A2,"ym")&" m "&DATEDIF(A1,A2,"md")&" d"
Where a1= DOB
a2 = DOD
Mike H
:
30:02 -0800, Mike H <[email protected]>
wrote:
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
On Thursday, February 21, 2008 12:02 PM Rick Rothstein \(MVP - VB\) wrote:
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 onthe
months being spanned. Hell, even years can be somewhat problematic give the
occurrence of leap years within time spans; but, when used by itselfas a
"rough" indicator of time span, this if fine; however, the accuracy implied
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
On Thursday, February 21, 2008 12:49 PM Niek Otten wrote:
<way more so than simply specifying years and days,>
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
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
"calendar months".  The meaning is clear (if you know the definition), but not
something that can be computed (easily) using DATEDIF.
--ron
[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
On Sunday, February 27, 2011 6:48 PM Susie Toews wrote:
=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")
Submitted via EggHeadCafe
SQL Server Table Valued Parameters / Types - Multiple Row Inserts
http://www.eggheadcafe.com/tutorials/aspnet/1b4677b6-3be6-4b68-897f-e...

 

[joeu2004]

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.

If B8 is the birth date and D8 is the date of death, then ostensibly
do:

=DATEDIF(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:

=DATEDIF(B8,D8,"y")
+(D8=EDATE(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]

=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")

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]

If B8 is the birth date and D8 is the date of death, then ostensibly
do:

=DATEDIF(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:

=DATEDIF(B8,D8,"y")
+(D8=EDATE(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.

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]

=DATEDIF(B8,D8,"y")
+(D8=EDATE(B8,12+12*DATEDIF(B8,D8,"y")))

[....]
Since th OP also wanted months and days, Datedif
may be inappropriate.

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]

Ron

I get 4 with either 2003 and 2007


Gord

 

[Ron Rosenfeld]

Ron

I get 4 with either 2003 and 2007


Gord

Gord,

Which SP do you have for 2007?

The function apparently broke in one of the SP's for 2007, but I can't recall if it was SP1 or SP2. I have SP2 and it definitely is broken.

 

[joeu2004]

The function apparently broke in one of the SP's for
2007, but I can't recall if it was SP1 or SP2. I have
SP2 and it definitely is broken.

According to Rick Rothstein, MVP: "The problem didn't come about with
Excel 2007, rather, it came about with Service Pack 2 for Excel 2007".

 

[Ron Rosenfeld]

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

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]

But I prefer a UDF to output my desired string: [....]
Function DateIntvl(d1 As Date, d2 As Date) As String

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:

=DATEDIF(A4,B4,"y")
+(B4=EDATE(A4,12+12*DATEDIF(A4,B4,"y")))
& " years, "
& MOD(DATEDIF(A4,B4,"m")
+(B4=EDATE(A4,1+DATEDIF(A4,B4,"m"))),12)
& " months, "
& B4-EDATE(A4,DATEDIF(A4,B4,"m")
+(B4=EDATE(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>=d2

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)

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")
DateIntvl2 = Mid(s, 3)

End Function

NOTE: "Or (yr = 0 And mnth = 0)" becomes unnecessary if you add the
d1>=d2 suggested error checking.

 

[joeu2004]

But I prefer a UDF to output my desired string: [....]
Function DateIntvl(d1 As Date, d2 As Date) As String

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

Obvious typo: 2/29/1948 and 2/28/1949.

Sigh, I've really gotten used to the Edit feature in the Answers
Forum ;-).

 

[Ron Rosenfeld]

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

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.

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 avoided DateDiff because of the same uncertainty as how bulletproof it is.

I did not bother to vet your Excel implementation.

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

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

Good suggestion