View Single Post
  #18   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 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
=========================