Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
Hi Ann,
Calculating the age at death is actually quite simple in Excel. You can use the DATEDIF function to calculate the difference between the birth date and the date of death in years, months, and days. Here's how you can do it:
The formula uses the DATEDIF function to calculate the difference between the birth date and the date of death in years, months, and days. The "&" symbol is used to concatenate the results of the three DATEDIF functions into a single text string.
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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: 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 |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Thu, 21 Feb 2008 06:30:02 -0800, Mike H
wrote: 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 |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
<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 "Rick Rothstein (MVP - VB)" wrote in message ... | 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 | |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Thu, 21 Feb 2008 12:02:14 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: 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 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 |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Thu, 3 Mar 2011 14:54:09 -0800 (PST), joeu2004 wrote:
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 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 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 |
#9
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Mar 3, 7:26 pm, Ron Rosenfeld wrote:
On Thu, 3 Mar 2011 14:54:09 -0800 (PST), joeu2004 wrote: 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. Good "assumption". I said as much in my follow-up errata that I posted nearly 4.5 hours before your response. You wrote: But how to handle that situation is not entirely clear, and also gets into legalities. 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. ;-) You wrote: 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 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 I wrote: "If you [sic] that is what you want, too, then do" the 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 "2/28/2009"-"1/29/2009" is 30. You wrote: 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. If you want to use an array and Join, that's 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") I wrote: 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 "IMHO". |
#10
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Thu, 3 Mar 2011 21:39:13 -0800 (PST), joeu2004 wrote:
Good "assumption". I said as much in my follow-up errata that I posted nearly 4.5 hours before your response. And had I seen it before I posted mine, I would have mentioned it. Or did you bring this up for some other reason that would contribute to our discussion? 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 never heard or read of that in the US. On the contrary.... The first below was cited in an article I read. The second is where I live. Haven't gone through any other legal research in the US. MICHIGAN VEHICLE CODE http://www.legislature.mi.gov/%28S%2...ary%20AND%2029 257.4a “Birthday” defined. Sec. 4a. “Birthday” shall mean any anniversary of the original date of birth, and all persons born on February 29 shall be deemed, for the purposes of this act, to have been born on March 1. ------------------------------------------------- Maine Motor Vehicle Code: http://www.mainelegislature.org/legi.../title29-A.pdf 29-A 1406. Expiration 3. Leap year birthday. For the purposes of this section, a person born on February 29th is deemed to have been born on March 1st. --------------------------------------------------- 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. Yes that is true, but in this one instance, I am uncertain how to treat that time interval. However, it is interesting that by using a previous version of my UDF, from 2006, (http://www.pcreview.co.uk/forums/hav...-t2506504.html) which I subsequently "updated" by calculating "years" first, gives the "1 year" result. This version has not had the "prettied up" output: ======================================= Option Explicit Function DateIntvl2(d1 As Date, d2 As Date) As String Dim temp As Date Dim i As Double Dim yr As Long, mnth As Long, dy As Long Dim Yrstr As String, Mnstr As String, Dystr As String Do Until temp d2 i = i + 1 temp = DateAdd("m", i, d1) Loop i = i - 1 temp = DateAdd("m", i, d1) yr = Int(i / 12) mnth = i Mod 12 dy = d2 - temp Yrstr = IIf(yr = 1, " yr ", " yrs ") Mnstr = IIf(mnth = 1, " month ", " months ") Dystr = IIf(dy = 1, " day", " days") DateIntvl2 = yr & Yrstr & mnth & Mnstr & dy & Dystr End Function ========================================= |
#11
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Mar 4, 4:12*am, Ron Rosenfeld wrote:
MICHIGAN VEHICLE CODE *http://www.legislature.mi.gov/%28S%2...lsivns%29%29/m... 257.4a Birthday defined. [....] Maine Motor Vehicle Code: * *http://www.mainelegislature.org/legi.../title29-A.pdf 29-A *1406. Expiration 3. Leap year birthday. For the purposes of this section, a person born on February 29th is deemed to have been born on March 1st. Thanks for those citation. I guess I was wrong about how uniform the treatment of Feb 29 anniversaries is across the US. In any case, I reiterate: my goal was to be consistent with EDATE's handling of the Feb 29 anniversary, not with any particular laws. You wrote: However, it is interesting that by using a previous version of my UDF [...] gives the "1 year" result. So we seem to be in "violent agreement". Independent of how you choose to handle the Feb 29 anniversary (an unrelated issue), the point is: it is no more correct to output "12 months" instead of "1 year" from a routine that breaks down date intervals into year/month/days than it would be to output "60 minutes" instead of "1 hour" from a routine that breaks down time intervals into hour/minute/seconds. I do not believe the first implementation of your UDF presented in this thread returns "12 months" for any other start/end date pair. And IMHO, there is no rational reason to think that it should for the Feb29-to-Feb28 interval -- to think that "12 months" has some special meaning specific to that unique circumstance. It is a simple defect -- which is the only point I was trying to make originally. |
#12
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I am running SP2...............not broke<g
Don't know if I ever installed SP1. Gord On Thu, 03 Mar 2011 15:47:18 -0500, Ron Rosenfeld wrote: On Thu, 03 Mar 2011 10:34:25 -0800, Gord Dibben wrote: 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. |
#13
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I am running SP2...............not broke<g
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) |
#14
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I also get 122
Gord On Fri, 4 Mar 2011 13:21:30 -0500, "Rick Rothstein" wrote: I am running SP2...............not broke<g 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) |
#15
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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) |
#16
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I would easily rule DATEDIF as more not reliable than it ever has been.
Good for quick and easy non-critical computations only. Gord On Fri, 4 Mar 2011 14:17:44 -0500, "Rick Rothstein" wrote: 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) |
#17
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On márc. 3, 18:43, joeu2004 wrote:
On Mar 3, 8:21 am, Ron Rosenfeld wrote: On Thu, 3 Mar 2011 04:46:14 -0800 (PST), joeu2004 wrote: =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. Thank you. I was interested in the YMD like solution. (well, erroneously I always write version 2007 however I got Off2010) |
#18
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Fri, 4 Mar 2011 08:02:02 -0800 (PST), joeu2004 wrote:
Independent of how you choose to handle the Feb 29 anniversary (an unrelated issue), the point is: it is no more correct to output "12 months" instead of "1 year" from a routine that breaks down date intervals into year/month/days than it would be to output "60 minutes" instead of "1 hour" from a routine that breaks down time intervals into hour/minute/seconds. I do not believe the first implementation of your UDF presented in this thread returns "12 months" for any other start/end date pair. And IMHO, there is no rational reason to think that it should for the Feb29-to-Feb28 interval -- to think that "12 months" has some special meaning specific to that unique circumstance. It is a simple defect -- which is the only point I was trying to make originally. Well, I don't know how the Feb 29 issue should be handled. I believe my original (2006) UDF gives "consistent" results. For the thread, here it is with the "prettied up" output: ====================================== Option Explicit Function DateIntvl(d1 As Date, d2 As Date) As String 'Note that if d1 = 29 Feb, the definition of a year 'may not be the same as the legal definition in a 'particular locale 'Some US states, for some purposes, declare a 'leapling's birthday on 1 Mar in common years; England 'and Taiwan declare it on Feb 28 Dim temp As Date Dim i As Double Dim yr As Long, mnth As Long, dy As Long Dim sOutput() As String Do Until temp d2 i = i + 1 temp = DateAdd("m", i, d1) Loop i = i - 1 temp = DateAdd("m", i, d1) yr = Int(i / 12) mnth = i Mod 12 dy = d2 - temp ReDim sOutput(0 To -(yr 0) - (mnth 0) - (dy 0) - 1) i = 0 If yr 0 Then sOutput(i) = yr & IIf(yr = 1, " Year", " Years") i = i + 1 End If If mnth 0 Then sOutput(i) = mnth & IIf(mnth = 1, " Month", " Months") i = i + 1 End If If dy 0 Then sOutput(i) = dy & IIf(dy = 1, " Day", " Days") DateIntvl = Join(sOutput, ", ") End Function ========================= |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Cemetery birth and death date sort | Excel Discussion (Misc queries) | |||
bar graph showing year of birth and death of several people | Charts and Charting in Excel | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
calculating an age | Excel Discussion (Misc queries) | |||
Not Calculating? | Excel Worksheet Functions |