View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
external usenet poster
 
Posts: 1,045
Default 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