Calculate time difference over days in hours

[velamurikrishna121]

I use time and date in the same field. I want the difference between two fields with date and time in hours and minutes. The two fields may have date with potential difference of upto 3 days.Presently using Access 2007.

Eg: Arrival 12:30 01-01-2012
Departure 14:45 04-01-2012
On hand 74:15 hrs.

This is my requirement. Can anyone help me how to do it in Queries and Forms.

 

[tehNellie]

Basic VBA to illustrate the method, the datediff() Function used like this can be done in both VBA and a query.

Sub Test()
  Dim intHours As Integer
  Dim intMinutes As Integer
  
  intHours = DateDiff("h", #1/1/2012 12:30:00 PM#, #1/4/2012 2:45:00 PM#)   
intMinutes = DateDiff("n", #1/1/2012 12:30:00 PM#, #1/4/2012 2:45:00 PM#) Mod (60)
  
  Debug.Print "Difference: " & intHours & ":" & intMinutes

take the difference in hours as a whole number

Applying Mod(60) to the difference between the two dates in minutes finds out the remaining minutes once the total hours are excluded

 

[apr pillai]

Copy and paste the following Code into a Standard Module:

Public Function HrsMin(ByVal startDate As Date, ByVal endDate As Date) As String
Dim diff As Double
diff = (endDate - startDate) * 24
HrsMin = Int(diff) & ":" & (diff - Int(diff)) * 60
End Function

Call the above function from a Query Column with StartDate and EndDate field values as shown below:

myTime: HrsMin([SDate], [EDate])

or from a Form Text Box:

=HrsMin([txtStartD], [txtEndD])