need help calculation Date / Time

[Jon123]

I have 2 fields Field1 = Start Date and Time Field 2 has the End Date and Time. How can I calculate the difference between them and display the difference in a field in the total Hours and minutes?

jom

 

[namliam]

Field2 - Field1 will calculate the difference just fine... just apply some format to the field to show the value you need.

Question, how hard can it be to imagine if you need the difference between two fields you substract them?

 

[Jon123]

Yes, I figured I could substract them but I dont know how to convert the outcome into hrs. Sorry

jon

 

[Jon123]

Ok I got it working but have a few questions. 1st what I did was, I created a module. Here is the code for my module.

Function GetElapsedTime(interval)
   Dim totalhours As Long, totalminutes As Long
   Dim days As Long, Hours As Long, Minutes As Long
   days = Int(CSng(interval))
   totalhours = Int(CSng(interval * 24))
   totalminutes = Int(CSng(interval * 1440))
   Hours = totalhours Mod 24
   Minutes = totalminutes Mod 60
   If days >= 1 Then
   days = days * 24
   Hours = Hours + days
   End If
   Dim nMinutes As Single
   Select Case Minutes
   Case 1 To 6
   nMinutes = 0.1
   Case 7 To 12
   nMinutes = 0.2
   Case 13 To 18
   nMinutes = 0.3
   Case 19 To 24
   nMinutes = 0.4
   Case 25 To 30
   nMinutes = 0.5
   Case 31 To 36
   nMinutes = 0.6
   Case 37 To 42
   nMinutes = 0.7
   Case 43 To 48
   nMinutes = 0.8
   Case 49 To 54
   nMinutes = 0.9
   Case 55 To 60
   nMinutes = 1
End Select
     [Forms]![Frm1totalhrsGtoG] = Hours & " Hrs " & Minutes & " Min "
GetTime_Exit:
Exit Function
GetTime_Error:
MsgBox Err.Description
Resume GetTime_Exit
End Function

Ok then on my form after an update I have this code

Call GetElapsedTime([WCComplete] - [WCStart])

So when this runs I do get the correct Hours and Minutes in the field TotalhrsGtpG. Which is cool but If I want to use this code for more calculations I have to copy the whole module and call it something else. Is there a way I can move this line [Forms]![Frm1totalhrsGtoG] = Hours & " Hrs " & Minutes & " Min " to the form so that I can Call the module and get the same results. Otherwise I would have to copy this module call it GetTime2 etc etc seems very bulky

 

[namliam]

All you need to do to convert them into hours, like I said is apply the required format to the field, like HH:MM

you can even add the "Hour" and "Min" into your format... Sorry for all your hard work, but forget the function anything and everything you want you can do without it.

 

[Jon123]

OK so if I format the field as short time I will get to hrs. but how I the hours and minutes to the answer?

 

[nanscombe]

Since we don't know exactly what you are using it for...

Function differenceInHHMM() takes two parameters:
dihStart - Start Date/Time (variant)
dihEnd - End Date/Time (variant)

Public Function differenceInHHMM(ByVal dihStart As Variant, ByVal dihEnd As Variant)
Dim varTemp As Variant, lngHours As Long, lngMinutes As Long
  
   varTemp = ""
  ' If either of the parameters is empty / missing jump to the end
  If Len(dihStart & vbNullString) = 0 Then GoTo differenceInHHMM_End
  If Len(dihEnd & vbNullString) = 0 Then GoTo differenceInHHMM_End
    
  ' Find difference between Start and End date/times in minutes.
  varTemp = DateDiff("n", dihStart, dihEnd)
  
  ' lngHours is the integer of the difference in varTemp divided by 60.
  lngHours = Int(varTemp / 60)
  
  ' lngMinutes is the remainder of the difference in varTemp - 60 * lngHours.
  lngMinutes = varTemp - (lngHours * 60)
  
  ' Form the output as required
    varTemp = lngHours & ":" & Format(lngMinutes, "00")
'  varTemp = lngHours & "hrs " & Format(lngMinutes, "00") & " mins"
'  varTemp = lngHours & ":" & Format(lngMinutes, "00")
  
differenceInHHMM_End:
  differenceInHHMM = varTemp
End Function

The output is a string as defined at the end. The function returns an empty string if either of the two parameters is missing or empty.

You could three textboxes; txtStart, txtEnd, txtDifference.
txtStart: Format General Date
txtEnd: Format General Date
txtDifference: Format (none), Control Source = differenceInHHHMM([txtStart],[txtEnd])

The textbox txtDifference would automatically calculate, but not store, the difference between the other textboxes.