The Number of Years, Months and Days Between Two Dates

[snb]

Hi Rick,

Nice function you wrote.
You challenged me to do so too.

Wouldn't this suffice ?

Function snb(x0 as string, x1 as string)
    x2 = DateDiff("m", x0, x1) - IIf(Day(x0) > Day(x1), 1, 0)
    c01 = x2 \ 12
    c02 = x2 Mod 12
    c03 = Day(x1) - Day(x0) + IIf(Day(x1) >= Day(x0), 0, Day(CDate(x1) - Day(x1)))
    snb = c01 & " year" & IIf(c01 = 1, ", ", "s, ") & c02 & " month" & IIf(c02 = 1, ", ", "s, ") & c03 & " day" & IIf(c03 = 1, "", "s")
End Function

sub tst()
  msgbox snb("02-04-2011","22-02-2012")
end sub

[Rick Rothstein]

Hi Rick,

Nice function you wrote.
You challenged me to do so too.

Wouldn't this suffice ?

Function snb(x0 as string, x1 as string)
    x2 = DateDiff("m", x0, x1) - IIf(Day(x0) > Day(x1), 1, 0)
    c01 = x2 \ 12
    c02 = x2 Mod 12
    c03 = Day(x1) - Day(x0) + IIf(Day(x1) >= Day(x0), 0, Day(CDate(x1) - Day(x1)))
    snb = c01 & " year" & IIf(c01 = 1, ", ", "s, ") & c02 & " month" & IIf(c02 = 1, ", ", "s, ") & c03 & " day" & IIf(c03 = 1, "", "s")
End Function

I am glad you posted your message because I think it led me to a hidden flaw in the underlying algorithm I used for my function (and which your code duplicates). First though, there is a problem with your code as posted... it appears to never find a difference of zero days between any dates. See the line highlighed below in purple... you cannot go from 66 years, 3 months, 1 day to 66 years, 3 months, 31 days for a difference of one day in the start date when keeping the end date steady. Your code appears to do a similar jump at every zero-day difference.

	A	B	C	D	E
55			snb's formula		rick's formula
56	2/25/1946	6/7/2012	66 years, 3 months, 13 days		66 years, 3 months, 13 days
57	2/26/1946	6/7/2012	66 years, 3 months, 12 days		66 years, 3 months, 12 days
58	2/27/1946	6/7/2012	66 years, 3 months, 11 days		66 years, 3 months, 11 days
59	2/28/1946	6/7/2012	66 years, 3 months, 10 days		66 years, 3 months, 10 days
60	3/1/1946	6/7/2012	66 years, 3 months, 6 days		66 years, 3 months, 6 days
61	3/2/1946	6/7/2012	66 years, 3 months, 5 days		66 years, 3 months, 5 days
62	3/3/1946	6/7/2012	66 years, 3 months, 4 days		66 years, 3 months, 4 days
63	3/4/1946	6/7/2012	66 years, 3 months, 3 days		66 years, 3 months, 3 days
64	3/5/1946	6/7/2012	66 years, 3 months, 2 days		66 years, 3 months, 2 days
65	3/6/1946	6/7/2012	66 years, 3 months, 1 day		66 years, 3 months, 1 day
66	3/7/1946	6/7/2012	66 years, 3 months, 31 days	X	66 years, 3 months, 0 days
67	3/8/1946	6/7/2012	66 years, 2 months, 30 days		66 years, 2 months, 30 days
68	3/9/1946	6/7/2012	66 years, 2 months, 29 days		66 years, 2 months, 29 days
69	3/10/1946	6/7/2012	66 years, 2 months, 28 days		66 years, 2 months, 28 days

Now, for the flaw. Look at the two lines I highlighted in red. The end dates are the same and the start dates differ by one day, yet our code indates a four day difference... and I cannot find where those four days ever get "made up for" later in the chart which means, I think, that the error is cummulative in some way. I would appreciate your thought (actually, anyone's thoughts) on this. I'll be re-examining my code later on today or tommorow as time permits, but for now, I will put a caution note on my original message.