DateDiff returns the wrong information.

Question

I'm trying to claculate the difference between two times in a macro.

I'm using the following command:

DateDiff("n",[Forms]![Retail SLA Form]![SLA Time],[Forms]![Retail SLA Form]![End Time])

The value is to be stored in a field called **[Forms]![Retail SLA Form]![Date]**which is to be stored as a time HH:MM but the value returned is a date 5/4/1899.

Every article that I read says to use DateDiff but for some reason I can't seem to get it to work correctly.

The time I am calculating is 04:00 - 03:00 which should return 01:00.

These times will change depending on the information entered into the two fields - right now I am just using some test data.

Both fields that the calculation is to be performed on is defined in the table as a 'Short Date' and on the form as well.

Does anyone have any ideas as to what I am doing wrong?

Thanks

Answer 1

Thanks - This is the final command that I used:

(DateDiff('n',[Forms]![Retail SLA Form]![SLA Time],[Forms]![Retail SLA Form]![SLA End Time])/1440)

It now returns the correct time value.

Answer 2

The problem is you are formatting the control as a date value, not a time value. The DateDiff does not return a time value. It returns the number of minutes as an integer value. To get it to be a time value you would have to divide by 1440 (the number of minutes in a day).

Answer 3

How can I use the datediff to calcualte the time difference between a time that starts before midnight and a time that ends after midnight?

The following function will handle this:

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _

            Optional blnShowdays As Boolean = False) As String

    ' Returns duration between two date/time values

    ' in format hh:nn:ss, or d:hh:nn:ss if optional

    ' blnShowDays argument is True.

    ' If 'time values' only passed into function and

    ' 'from' time is later than or equal to 'to' time, assumed that

    ' this relates to a 'shift' spanning midnight and one day

    ' is therefore subtracted from 'from' time

    Dim dtmTime As Date

    Dim lngDays As Long

    Dim strDays As String

    Dim strHours As String

    ' subtract one day from 'from' time if later than or same as 'to' time

    If dtmTo <= dtmFrom Then

        If Int(dtmFrom) + Int(dtmTo) = 0 Then

            dtmFrom = dtmFrom - 1

        End If

    End If

    ' get duration as date time data type

    dtmTime = dtmTo - dtmFrom

    ' get whole days

    lngDays = Int(dtmTime)

    strDays = CStr(lngDays)

    ' get hours

    strHours = Format(dtmTime, "hh")

    If blnShowdays Then

        TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")

    Else

        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _

            Format(dtmTime, ":nn:ss")

    End If

End Function

Note that it returns a string, in the format hh:nn:ss by default, so you cannot do date/time arithmetic on the returned value.  You can see how it works in the debug window:

? TimeDuration(#22:00#,#02:04#)

04:04:00

whereas:

? TimeDuration(#20:00#,#22:04#)

02:04:00

Both are correct in the context of the former's times being on subsequent days.  The function will of course also work if full date/time values are passed into it, in which case the values can be more than one day apart if necessary.

Answer 4

How can I use the datediff to calcualte the time difference between a time that starts before midnight and a time that ends after midnight?

For instance I want to calculate the difference between 22:00 and 02:04. The result should be 4:04

This command works for times that end before midnight:

(DateDiff('n',[Forms]![Retail SLA Form]![SLA Time],[Forms]![Retail SLA Form]![SLA End Time])/1440)

Thanks

Answer 5

DateDiff does not return a date/time value. It returns a number (long). In this case (using "n" parameter) it will return 60 (minutes).

Peter