The Number of Years, Months and Days Between Two Dates

**snb** · 06-08-2012, 10:11 PM

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:

Code:

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** · 06-08-2012, 10:30 PM

Originally Posted by 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 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.