Calculating age of death

[joeu2004]

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.

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. ;->)

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.

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".

 

[Ron Rosenfeld]

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?

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/(S(cq...mcl-257-4a&query=on&highlight=february AND 29

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/legis/statutes/29-A/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/have-two-days-and-want-difference-days-months-year-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
=========================================

 

[joeu2004]

MICHIGAN VEHICLE CODE  http://www.legislature.mi.gov/(S(cqpjgqz4zicjfcmegalsivns))/m...
257.4a Birthday defined. [....]
Maine Motor Vehicle Code:    http://www.mainelegislature.org/legis/statutes/29-A/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.

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.

 

[Gord Dibben]

I am running SP2...............not broke<g>

Don't know if I ever installed SP1.


Gord

 

[Rick Rothstein]

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)

 

[Rick Rothstein]

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".

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]

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)

 

[Rick Rothstein]

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)

 

[Gord Dibben]

I would easily rule DATEDIF as more not reliable than it ever has been.

Good for quick and easy non-critical computations only.


Gord

 

[Konczér, Tamás]

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

 

[Ron Rosenfeld]

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