Calculate response time in working hours between two dates

Hi - I am new to the forum and hopefully someone can give me a hand...I am trying (in vain so far) to calculate the response time to service calls, based on working hours.

Basic data I have is,-
Date call received (DD:MM:YY HH:MM)
Date call completed (DD:MM:YY HH:MM)

Working hours are from 08:00 to 17:00
Working days are all days, so 7 days a week 52 weeks a year

So, for example a call is logged on 06/06/2012 09:07 and completed on 06/06/2012 11:48 the answer is simply 02:41. Easy, because both start and end date are on the same day and in working hours.
But, if a call is logged on 06/06/2012 14:52 and completed on 07/06/2012 14:05, I can't for the life of me establish how to factor in working hours and come up with the right answer (which should be 08:13).

I have played around with Networkdays for ages but as we operate 7 days a week I'm not sure this applies??

Your help would be greatly appreciated.
Many thanks,
Andy

Hi,

No idea if this is the easiest way but it worked for me. Assuming your log time is in a and the completed time in b then.

=(TIME(17,0,0)-TIME(HOUR(A2),MINUTE(A2),SECOND(A2)))+(9-(TIME(17,0,0)-((TIME(HOUR(B2),MINUTE(B2),SECOND(B2)))))*24)/24

HTH
Steve

Many thanks Steve.
This is giving me the answers I have been getting, which when you calculate manually are just wrong!! Let me show you an example...
Log date/time - 06/06/2012 09:07 & Completed date/time - 06/06/2012 11:48 - this is returning 11hrs:41mins when clearly its not!!

Also, when a log date/completed date spans more than a day, it doesn't add up all the working hours:
Log date/time - 22/05/2012 15:38 & completed date/time - 30/05/2012 11:24 - this returns 04hrs:46mins when its at least 63 'working hours'.

Any ideas???
Thanks again...

pl see the attached file.
Formula is in F column.

HI kvsrinivasamurthy,
GENIUS!! Works perfectly - thanks very much indeed.
Andy

Hi daddylonglegs - Many thanks to you too for your help and advise. I have run with this now and it all works perfectly.
Andy

Dear daddylonglegs,
Thanks for correction.

Hello- Thank you for this information (very informative and am learning a lot!), however; I am having issues with internet lead response times with different business hours than listed in previous posts. I was hoping that I can get some help on this.

Our business hours are Monday-Friday 9:00-21:00, and Saturday 9:00-17:00, closed on Sunday.

Some lead creation (or inception) dates fall on Saturday and are not responded to until Monday which is causing issues using the aforementioned formula. Here are some of the scenarios:

4/8/2019 20:19 4/9/2019 1:55
4/8/2019 12:20 4/8/2019 17:21
4/6/2019 20:09 4/8/2019 15:53
4/6/2019 10:57 4/6/2019 16:50
4/5/2019 20:08 4/6/2019 16:48
4/5/2019 17:29 4/5/2019 22:36

Is there a formula so these scenarios? Your help is appreciated!!! Many thanks

Originally Posted by CG2304

Hello- Thank you for this information (very informative and am learning a lot!), however; I am having issues with internet lead response times with different business hours than listed in previous posts. I was hoping that I can get some help on this.

Our business hours are Monday-Friday 9:00-21:00, and Saturday 9:00-17:00, closed on Sunday.

Some lead creation (or inception) dates fall on Saturday and are not responded to until Monday which is causing issues using the aforementioned formula. Here are some of the scenarios:

4/8/2019 20:19 4/9/2019 1:55
4/8/2019 12:20 4/8/2019 17:21
4/6/2019 20:09 4/8/2019 15:53
4/6/2019 10:57 4/6/2019 16:50
4/5/2019 20:08 4/6/2019 16:48
4/5/2019 17:29 4/5/2019 22:36

Is there a formula so these scenarios? Your help is appreciated!!! Many thanks

Hello everyone--this has been a very valuable post, despite its age. I have been learning a lot about it and would also like to know if anyone here was able to figure out the formula CG2304 was asking for.