Calculate time lapse with exclusions

[kmacd6951]

I found post in the Excel forum that address this, but looking for an Access solution.

Right now I am calculating the difference in time (CRT) between when a call came in and when someone closed (or responded) to that call.

DateDiff("n", [ADATECREATE], [ADATERESOLVED])

I would like to take this a step further however and take business hours into consideration. So, if someone calls after 8PM Friday and before 8AM Monday the CRT doesn't look so horrible.
Ideally if they called Friday at 10:00PM and we responded on Monday at 8:45AM, the CRT should only be 45 mins (difference from when we began business again and when we closed the call).

Also the format of my ADATECREATE and ADATERESOLVED fields is:
for example: 04/16/2004 4:16:31 PM

Has anyone created a Module already that does something like this? So I don't have to start from scratch?

Thanks
Katie

 

[kmacd6951]

For one part of the analysis on this data, I am exporting the Access data to Excel so I can do Data Analysis/Histogram on the Response time.

I need to be able to measure the % of calls responded to in less than 30 mins for example.

So, it would be best I guess to have the response time come out as 126 for 2 hours and 6 mins.

Does that make sense?

Thanks

 

[CT Witter]

At the end of the code change:

nhw = Format(Int(TotalNetHoursWorked / 60), "00") & ":" & Format(TotalNetHoursWorked Mod 60, "00")

to:

 nhw = TotalNetHoursWorked

HTH,
CT

 

[kmacd6951]

Just a note.. in case anyone decides to use this....

I added another group of logic to the code, as I realized the way it exists, if a call came in after midnight, but before 8:15 AM, the response time was not working correctly.

So, where it was for example:

ElseIf Format(StartingDateTime, "DDDD") = "Monday" And Hour(StartingDateTime) > 20 Then

    StartingDateTime = Format(DateAdd("d", 1, StartingDateTime), "MM/DD/YY") & " 8:15 AM"

Now, I have:

ElseIf Format(StartingDateTime, "DDDD") = "Monday" And Hour(StartingDateTime) < 8 Then

    StartingDateTime = Format(DateAdd("d", 0, StartingDateTime), "MM/DD/YY") & " 8:15 AM"
    
ElseIf Format(StartingDateTime, "DDDD") = "Monday" And Hour(StartingDateTime) > 20 Then

    StartingDateTime = Format(DateAdd("d", 1, StartingDateTime), "MM/DD/YY") & " 8:15 AM"