[....]
Your function returns "12 months" instead of "1 year" for the dates
2/29/1948 and 2/29/1949 (d1 and d2).
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 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 did not bother to vet your Excel implementation.
But an errata of my own.... I made a mistake in a last-minute
misguided "simplification" of the last subexpression (days). I should
have written:
=3DDATEDIF(A4,B4,"y")
+(B4=3DEDATE(A4,12+12*DATEDIF(A4,B4,"y")))
& " years, "
& MOD(DATEDIF(A4,B4,"m")
+(B4=3DEDATE(A4,1+DATEDIF(A4,B4,"m"))),12)
& " months, "
& B4-EDATE(A4,DATEDIF(A4,B4,"m")
+(B4=3DEDATE(A4,1+DATEDIF(A4,B4,"m"))))
& " days"
-----
Simplified DateIntvl:
Option Explicit
Function DateIntvl(d1 As Date, d2 As Date) As String
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long
Dim s As String
'NOTE: It would be prudent for the type of DateIntvl
'to be Variant and return an appropriate CVErr() if
'd1>=3Dd2
yr =3D Year(d2) - Year(d1) + (d2 < DateSerial(Year(d2), Month(d1),
Day(d1)))
i =3D 0
Do Until DateAdd("m", yr * 12 + i, d1) > d2
i =3D i + 1
Loop
mnth =3D i - 1
dy =3D d2 - DateAdd("m", yr * 12 + mnth, d1)
If yr > 0 Then s =3D ", " & yr & IIf(yr =3D 1, " year", " years")
If mnth > 0 Then _
s =3D s & ", " & mnth & IIf(mnth =3D 1, " month", " months")
If dy > 0 Or (yr =3D 0 And mnth =3D 0) Then _
s =3D s & ", " & dy & IIf(dy =3D 1, " day", " days")
DateIntvl2 =3D Mid(s, 3)
End Function
NOTE: "Or (yr =3D 0 And mnth =3D 0)" becomes unnecessary if you add the
d1>=3Dd2 suggested error checking.