I Need a formula to calculate the Turn Around Time

[hairajeshm]

I need a formula to calculate Turn Around Time. Find below the sample data and details for your reference.

Received Date & Time: 10/29/2010 3:27:00 PM
Completed Date & Time: 11/1/2010 3:57:32 AM
Business Hours: 08:00 AM to 05:00 PM
Non Business hours: 05:00 PM to 08:00 AM
Weekdays : Monday to Friday
Weekends: Saturday & Sunday

If I calculate TAT for this time period it should show me 1 hour 33 minutes, why because business hours are closing by 5:00 PM on 10/29 so it should not calculate duration after 5:00 PM to until 08:00 AM on next day, however 10/29 is Friday so formula should calculate the duration from 10/29 Friday 3:37 PM to 10/29 5:00 PM and again it should calculate from 8:00 AM on 11/01 Monday and formula should skip Saturday & Sunday.

In this scenario I have completed the task before 8:00 AM on 11/01 so it should show duration of 1hour 33 minutes.

Please let me know if you need any more details

 

[Avi2017]

Re: I Need a formual to calculate the Turn Around Time

If you have start time/date in A2 and end time/date in B2 you can use this formula in C2

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00")

format C2 as [h]:mm

However, while using this formula for 02/05/2017 12:01 - 05/05/2017 10:18, it's coming 1:47 he only, which is wrong. Please suggest.

 

[James006]

Re: I Need a formual to calculate the Turn Around Time

Hello,

Based on your input ...the actual result is 25:17 ... i.e 25 hours and 17 minutes ... do not forget to Format Custom As [h]:mm

Hope this will help

 

[Avi2017]

Re: I Need a formual to calculate the Turn Around Time

Hello,

Based on your input ...the actual result is 25:17 ... i.e 25 hours and 17 minutes ... do not forget to Format Custom As [h]:mm

Hope this will help

Please provide me full formula for the same, wherein it's come 25:17. Thank you

 

[James006]

Re: I Need a formual to calculate the Turn Around Time

With cell A2 : 02/05/2017 12:01

and cell B2 : 05/05/2017 10:18

then in cell C2 : =(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00")

Do not forget to format cell C2 : Format Custom As [h]:mm

 

[Avi2017]

Re: I Need a formual to calculate the Turn Around Time

With cell A2 : 02/05/2017 12:01

and cell B2 : 05/05/2017 10:18

then in cell C2 : =(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00")

Do not forget to format cell C2 : Format Custom As [h]:mm

Got it - thank you

 

[James006]

Re: I Need a formual to calculate the Turn Around Time

You are welcome ...

 

[msansari]

Re: I Need a formual to calculate the Turn Around Time

=(NETWORKDAYS(A2,B2,H$1:H$10)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2,H$1:H$10),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2,H$1:H$10)*MOD(A2,1),"17:00","8:00")[/QUOTE]

Above is workable for me too. however I just wanted to change my working hrs time as I work for USA from india.
How should I change working hrs on above formula my working hrs as per india is close to 1730hrs to 0230hrs(next day)?

 

[Negip]

Re: I Need a formual to calculate the Turn Around Time

Hi I have used the formulae but the result (22:03 in hh:mm format) is wrong, below is the sample data I am using. Can you help
Received Date & Time: 02/10/2017 10:49:45
Completed Date & Time: 16/10/2017 14:53:31
Business Hours: 08:00 AM to 05:00 PM
Non Business hours: 05:00 PM to 08:00 AM
Weekdays : Monday to Friday
Weekends: Saturday & Sunday

 

[barry houdini]

Re: I Need a formual to calculate the Turn Around Time

.....22:03 in hh:mm format...

Hello Negip, welcome to MrExcel

When you use hh:mm format you get "clock time" which never exceeds 23:59 - change to custom format [h]:mm to see elapsed time - for your example you get 94:03

 

[adam_wright]

This formula has worked great for me but to be sure that I can use it confidently I need to understand what it is doing. Can anyone break it down for me ? especially the use of IF.Thankyou