Calculating age of death
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
|