The Number of Years, Months and Days Between Two Dates

[snb]

Hi Rick,

I fear you used an earlier version of my code.
I amended it to perform the zero days difference calculation.
It's so slight you can easily overlook it.

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

[Rick Rothstein]

I fear you used an earlier version of my code.
I amended it to perform the zero days difference calculation.
It's so slight you can easily overlook it.

Yes, I have been looking at the output from our formulas since early yesterday morning, so the odds are I grabbed your code before you edited it. Any thoughts on the other problem I raised (which both our codes exhibit)?

[snb]

Yes, it is no problem, unless you assume that a 'month' is a fixed quantity. Since it isn't it will produce odd results at first glance only.

From 28 feb to 28 may is 4 months, until the 7th of june is 29,30,31 may + 7 days in june: 10 days in total
From 01 march to the first of june is also 4 months (but comprising more days than the 3 months from 28 feb to 28 may).
If you count in days, the difference must be 1, if you count in months & days, the result will differ dependent of the period in the year you are counting with.

That's the nice thing about dates: it's a mixed 365,25 -number system, 12-number system, alternating 28,29,30,31-number system.

So both our methods produce the correct results.

Maybe a slight modification to make it more elegant:

Function snb(x0 As String, x1 As String)
    x2 = DateDiff("m", x0, x1) + (Day(x1) < Day(x0))
    c01 = x2 \ 12
    c02 = x2 Mod 12
    c03 = Day(x1) - Day(x0) -(Day(x1) < Day(x0)) * 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

[Rick Rothstein]

Yes, it is no problem, unless you assume that a 'month' is a fixed quantity. Since it isn't it will produce odd results at first glance only.

From 28 feb to 28 may is 4 months, until the 7th of june is 29,30,31 may + 7 days in june: 10 day in total
From 01 march to the first of june is also 4 months (but comprising more days than the 3 months from 28 feb to 28 may).
If you count in days, the difference must be 1, if you count in months & days, the result will differ dependent of the period in the year you are counting with.

That's the nice thing about dates: it's a miixed 365,25 -number system, 12-number system, alternating 28,29,30,31-number system.

So both our methods produce the correct results.

Yes, but of course you are right. When I quickly scanned the output list, the 4-day gap caught my eye and I didn't pay attention to the February/March switch-over. I am going to blame my confusion on not enough sleep and nowhere near enough coffee to compensate. Thanks for following up with this. Much appreciated.

[snb]

A nice occurrence of a bug 'between the ears' instead of in the code ,,,,