MS Access 2010: How to calculate the difference between two dates & times

[foyasoul]

Hi, I have a team of staff that manage a help desk. The hours are from 8:00 am to 5:00 pm. I would like to measure the response time, meaning the amount of time that it takes from when a ticket is assigned to our workgroup to when the ticket is acknowledged.


Here's the code I borrowed from another thread, but the calculations aren't accurate.

For example

OpenDate is 8/1/13 7:48 AM


AcknowledgedByTechTime is 08/01/2013 9:33:50 AM

The result is 1 hrs 45 min, but the correct result should be 1 hrs 33 min

I do not want to count the time prior to 8:00 am or after 5:00 pm.

---

Public Function WorkingHrsMins(OpenDate As Date, AcknowledgedByTechTime As Date) As String
'....................................................................
' Name:  WorkingHrs
' Inputs:   StartDate As Date & time
'  EndDate As Date & time
' Returns: Integer - number of hours not inclusive of weekends
' Author: Arvin Meyer
' Date:  February 19, 1997
' Modified by Steve S (ssanfu)
' Date: April 11, 2012
' Comment: Accepts two dates and returns the number
' of work hours and minutes between 8am and 5pm
' Note that this function does not account for holidays.
'....................................................................
   On Error GoTo Err_WorkingHrs


   Const cBeginingTime As Date = #8:00:00 AM#
   Const cEndingTime As Date = #5:00:00 PM#


   Dim intCountDays As Integer
   Dim intHours As Integer
   Dim intMinutes As Integer
   Dim intTotalMin As Integer
   Dim dtStart As Date
   Dim dtEnd As Date
   Dim dtTemp As Date
   Dim tmStart As Date
   Dim tmEnd As Date


   intMinutes = 0
   intHours = 0
   intCountDays = 0
   intTotalMin = 0
   WorkingHrsMins = 0


   'check end date > start date
   If AcknowledgedByTechTime < OpenDate Then
      dtTemp = OpenDate
      OpenDate = AcknowledgedByTechTime
      AcknowledgedByTechTime = dtTemp
   End If


   'get just the date portion
   dtStart = Int(OpenDate)
   dtEnd = Int(AcknowledgedByTechTime)
   'get just the time portion
   tmStart = OpenDate - dtStart
   tmEnd = AcknowledgedByTechTime - dtEnd


   If dtStart = dtEnd Then
      intTotalMin = DateDiff("n", tmStart, tmEnd)
   Else
      'skip the first day
      dtStart = dtStart + 1


      Do While dtStart < dtEnd
         'Make the above < and not <= to not count the EndDate


         Select Case Weekday(dtStart)
            Case Is = 1, 7
               'do nothing
            Case Is = 2, 3, 4, 5, 6
               intCountDays = intCountDays + 1
         End Select
         dtStart = dtStart + 1
      Loop
      intTotalMin = intCountDays * 720


      'first day minutes
      intTotalMin = IIf(intTotalMin + DateDiff("n", tmStart, cEndingTime) < 0, 0, intTotalMin + DateDiff("n", tmStart, cEndingTime))
      'Last day minutes
      intTotalMin = IIf(intTotalMin + DateDiff("n", cBeginingTime, tmEnd) < 0, 0, intTotalMin + DateDiff("n", cBeginingTime, tmEnd))




   End If
   intHours = intTotalMin \ 60   'hours
   intMinutes = intTotalMin Mod 60   'minutes


   'return value
   WorkingHrsMins = intHours & " hrs  " & intMinutes & " min"


Exit_WorkingHrs:
   Exit Function


Err_WorkingHrs:
   Select Case Err


      Case Else
         MsgBox Err.Description
         Resume Exit_WorkingHrs
   End Select


End Function

[June7]

So do you want to eliminate ticket initiated before or after those range ends or round up/down the time? Why would you not want to consider all the time spent on ticket?

[foyasoul]

Hi, I want to exclude that segment of time. So, like in the example I provided, the ticket came in or was assigned to our workgroup at 7:48 am. The staff's shift doesn't begin until 8:00 am, so I want to exclude the 12 min from counting towards how long it took them to acknowledge the ticket, because they weren't working when the ticket was actually opened. I hope that makes sense.

[ssanfu]

Add the lines in blue to your code:

   'get just the date portion
   dtStart = Int(OpenDate)
   dtEnd = Int(AcknowledgedByTechTime)
   'get just the time portion
   tmStart = OpenDate - dtStart
   tmEnd = AcknowledgedByTechTime - dtEnd

'   check the starting time- set the starting to 8:00 if before 8AM
   If tmStart < cBeginingTime Then
      tmStart = cBeginingTime
   End If

'   check the ending time - set the ending time to 5PM if after 5pm
   If tmEnd > cEndingTime Then
      tmStart = cEndingTime
   End If

[cphelps]

Then I would be doing another If statement to set dtTempt to be the OpenDate if it falls between 8am and 5pm, or if it falls outside of these hours then you need to set dtTemp to be 8am for the appropriate day (e.g if the OpenDate is after midnight and before 8am then set it for 8am for that day but if it is after 5pm but before midnight set it for 8am the next day).

[foyasoul]

ssanfu - Perfect!!! Thank you so much for helping me!!! I really did try to figure it out on my own, but I have little to no experience. Again, thanks for the help! I appreciate everyone's responses!