Time Difference Calculation

[Raneil]

Hello:

I have a report with three text boxes:

Box 1 contains an employee's total weekly hours in hours and minutes: 40:00
Box 2 contains the actual total hours worked for the week: 35:30
Box 3 is the difference between Box 1 minus Box 2

I tried all the date/time functions to calculate the difference between Box 1 and Box 2 and none of them worked. I keep on getting an error message. Does anybody know how to calculate the difference between two sets of time (in hours and minutes)?

Thank you in advance.

 

[raskew]

Hi -

The reason the date/time functions aren't working for you is that you're not dealing with times (e.g. 6:58:46 PM ) but rather with numerical representations of periods of elapsed time, expressed (as strings) in hh:nn format. In other words, if you were to say 'Yesterday it took me 1 hour and 15 minutes (01:15) to drive home.", that doesn't have anything to do with actual date/time--it's rather a measurement of hours and minutes.

Here's a UDF that will allow you to input two periods of elapsed time and return the difference (as a string) in hh:nn format. Copy/paste to a new module then call if from the debug (immediate) window as shown in the comments.

HTH - Bob

Function ElapTimeFig(pstartT As String, pendT As String) As String
'***********************************************
're:  http://www.access-programmers.co.uk/forums/showthread.php?t=103535
'Purpose:   Display difference in hh:mm format
'           between two periods of elapsed time
'           also in hh:mm format
'coded by:  raskew
'Inputs:    from debug (immediate) window
'           1) ? ElapTimeFig("40:00", "35:30")
'           2) ? ElapTimeFig("35:30", "40:00")

'Outputs:   1) 04:30
'           2) -04:30
'***********************************************

Dim startHold As Integer
Dim endHold   As Integer
Dim intHold   As Integer

    startHold = 60 * val(Left(pstartT, 2)) + val(Mid(pstartT, 4, 2))
    endHold = 60 * val(Left(pendT, 2)) + val(Mid(pendT, 4, 2))
    intHold = startHold - endHold
    
    ElapTimeFig = Format(str(intHold \ 60), "00") & ":" & Abs(Format(str(intHold Mod 60), "00"))

End Function

 

[Raneil]

raskew,

Thank you very much for the help and explanation. Now I can rest my brain -until the next challenge.

I did modify your code above a little bit so that the minutes always show as "00" instead of just "0". I removed the "Abs" in the following line of code:

ElapTimeFig = Format(Str(intHold \ 60), "00") & ":" & Abs(Format(Str(intHold Mod 60), "00"))

Thank you.

Raneil