DateDiff only good for Day Calculations

[briancoats]

By the way, I recently found out about the DateDiff function and I thought that it would be a great tool to use. I have found that it is only good for day calculations. I will give you a for instance.

DateDiff("01/30/2003","02/02/2003&quot returns 1

This is only four days, not a month. Ridiculous. Just thought you might like to know so you can keep your eyes peeled for this sort of thing. (It does the same thing with years, though the help warnes you about that.)

Brian Coats

 

[zemp]

I think it's actually three days, but,

DateDiff("d", "01/30/2003", "02/02/2003&quot returns 3.

The "d" is used to specify that we want the number of days. Check the help files for the other constants.

Thanks and Good Luck!

zemp

 

[robctech]

Check your MSDN for proper calls to the DateDiff function. You can get down to actual seconds between 2 dates if you want.

Hope this helps

 

[CCLINT]

>DateDiff only good for Day Calculations

With that logic you will find it is not good for days either:

DateDiff("d","01-01-2003 23:59:59",","02-01-2003 00:00:01&quot

Please note that here only 1 second has passed, but DateDiff returns 1 day passed.

DateDiff returns a number of intervals.

As robctech says, you need to check the documention for the proper use.
There is a way to use it properly.

 

[briancoats]

OK, so how would I get it to tell me that 0 months have passed from, january 30 to feb 02, and I didn't mean to count january 30 in the count earlier, just got a little flustered.

Brian

BTW: I have worked around this problem by writing my own thing specific to my application, but would like to know what is going on with this datediff thing.

 

[robctech]

Here is a function from one of my pieces of code that displays how long my program has been running. You should be able to break it down to use it for your application

Private Function Format_Up_Time()
    Dim DHMCalc As Long
    Dim DHMDisplay
    
    If Not ServerRunning Then Server_Up_Time = CDate(Date & " " & Time)
    '//Lets Get the Total Number of Days Running
    DHMCalc = DateDiff("n", Server_Up_Time, Format(Date & " " & Time, "mm/dd/yy hh:mm AMPM"))
    If DHMCalc < 0 Then DHMCalc = DHMCalc * -1
    '//First Lets See how many Days, We only want values of 1 or greater
    If DHMCalc >= 1440 Then
        If InStr(Round(DHMCalc / 1440, 5), &quot;.&quot;) > 0 Then
            DHMDisplay = Left((Round(DHMCalc / 1440, 5)), InStr(Round(DHMCalc / 1440, 5), &quot;.&quot;) - 1)
        Else
            DHMDisplay = Round(DHMCalc / 1440, 5)
        End If
    Else
        DHMDisplay = 0
    End If
    Format_Up_Time = DHMDisplay & IIf(DHMDisplay = 1, &quot; Day&quot;, &quot; Days&quot;)
    '//Second we want total number of hours Less then 24
    DHMCalc = DHMCalc - (DHMDisplay * 1440)
    '//Now lets calculate and display the total number of hours
    If DHMCalc >= 60 Then
        If InStr(Round(DHMCalc / 60, 6), &quot;.&quot;) > 0 Then
            DHMDisplay = Left((Round(DHMCalc / 60, 5)), InStr(Round(DHMCalc / 60, 5), &quot;.&quot;) - 1)
        Else
            DHMDisplay = Round(DHMCalc / 60, 5)
        End If
    Else
        DHMDisplay = 0
    End If
    Format_Up_Time = Format_Up_Time & &quot; &quot; & DHMDisplay & IIf(DHMDisplay = 1, &quot; Hour&quot;, &quot; Hours&quot;)
    '//Third, Lets subtract the number of hours we are displaying
    DHMCalc = DHMCalc - (DHMDisplay * 60)
    '//DHMCalc should NEVER be greater then 59 so lets just create the display
    Format_Up_Time = Format_Up_Time & &quot; &quot; & DHMCalc & IIf(DHMCalc = 1, &quot; Minute&quot;, &quot; Minutes&quot;)
End Function

Hope this helps you out.

 

[CCLINT]

But it doesn't calculate months because???
How many days are in a month? This is the problem.

Ok:
June = 6th month
May = 5th month
6 - 5 = 1

You have to expect the SAME interval returned as you requested! The DateDiff with an interval of "m", will subtract the month in the start date from the month in the end date - and JUST the month parts, because that is what you requested.
This is correct...


Try this (not tested):
Date2 = CDate(Date2) 'Needed if Date2 is not stored in a date variable.

?DateDiff(&quot;m&quot;,Date1, Date2) + (DateSerial(Year(Date1),Month(Date1)+1,Day(Date1))<Date2)