Date and Time Response Times

[4evalearning]

Good Morning,

Could I please ask for your expertise in assisting me with a query I currently have. Without the use of macros.

I have created an incident tracker that captures the service requests my team action on a daily basis. Incidnets are received via email and then logged into our CRM tool, however reports are limited. I am looking to calculate the response time from the email being recieved and agent actioning the request.

I have reviewed various posts, however they do not seem to answer the query I have in full.

Hours of Operation: Monday - Friday 8:00 - 18:00 GMT
Weekends to be excluded, however emails are recieved so would need to calculate from Monday at 8:00.
UK Bank Holidays to be excluded and the calculation to be from the next working day at 8:00 to to the time picked up by the agent.

To uncomplicate things I have included 2 columns of data below:

Column A Column B
Email Received Date/Time Agent Actions Email
31/07/2015 17:40:00 03/08/2015 08:02:00
04/08/2015 19:45:00 05/08/2015 08:15:00
05/08/2015 04:37:00 05/08/2015 08:35:00
07/08/2015 09:31:00 07/08/2015 09:31:00
07/08/2015 09:40:00 07/08/2015 09:39:00
07/08/2015 10:19:00 07/08/2015 10:11:00
28/08/2015 17:11:00 01/09/2015 08:08:00
30/08/2015 19:14:00 01/08/2015 08:58:00

Thank you in advance for your assistance.

 

[DRSteele]

Apparently this is the first response to this post. And here goes: there is a function called NETWORKDAYS.INTL that might help you.

 

[DRSteele]

I anticipate you might need something like this: How to Calculate Net Work Hours Between Two Dates - Excel Tactics

 

[4evalearning]

DrSteele:- Thank you for your reply. Your assistance has resolved my issue and I can now close my dynamic dashboard project.

 

[DRSteele]

You're welcome! I'm glad I could help you advance your project.