DATEDIFF

[JACQUELINE]

I am calculating the difference between dates, I first
calculate
days : datediff ("d", date1, date2) It gives me 48 days.
months: datediff("m", date1, date2) It gives me 2 months.

Should that say 1 month instead of two?

jackie

 

[Bruce M. Thompson]

I am calculating the difference between dates, I first

calculate
days : datediff ("d", date1, date2) It gives me 48 days.
months: datediff("m", date1, date2) It gives me 2 months.

Should that say 1 month instead of two?

I can't seem to locate where I found some date functions that provided more
accuracy than the ones built into VBA, but you can try this (watch for line
wrap - it's all on one line):

datediff("m",Date1,Date2)-abs(dateadd("m",datediff("m",Date1,Date2),Date1)<Date2
)

This one was off the top of my head - I really haven't tested it thoroughly.

 

[Tim Ferguson]

days : datediff ("d", date1, date2) It gives me 48 days.
months: datediff("m", date1, date2) It gives me 2 months.

Should that say 1 month instead of two?

I have to confess a personal aversion to the DateDiff function, and always
roll my own. If you want completed months, then you can use

wYears = Year(Date2) - year(Date1)
wMonths = Month(Date2) - Month(Date1)
wDays = Day(Date2) - Day(Date1)

' handle the carries...
If wDays < 0 Then
wMonths = wMonths - 1
' don't bother carrying into the days
End If

' ditto
If wMonths < 0 Then
wYears = wYears - 1
wMonths = wMonths + 12
End If

wMonths = wMonths + 12 * wYears

You can always squash this down to one line if you really need to.

HTH


Tim F

 

[Rick Brandt]

I am calculating the difference between dates, I first
calculate
days : datediff ("d", date1, date2) It gives me 48 days.
months: datediff("m", date1, date2) It gives me 2 months.

Should that say 1 month instead of two?

DateDiff() returns "boundaries crossed". So in the case of DateDiff("m"...
the number returned is strictly how many First-of-the-Months are between
the two dates. For example...

DateDiff("m", #12/31/2002#, #1/1/2003#) = 1

....even though there is only one day difference.

If you want higher precision then you typically have to use DateDiff() on a
smaller interval (like Days) and then do the math yourself to return the
value you want.