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

 

[KUKRERH]

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

You can add the holiday range as the 3rd argument in each of the NETWORKDAYS functions, so if holidays are listede in H1:H10 change formula to the following:

=(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")

How do I calculate the TAT if Friday and Saturday are the weekends for my team?

 

[James006]

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

Hi,

For week-ends on Fridays and Saturdays, you can use 7 as the argument in the function Networkdays.intl ... :wink:

HTH

 

[KUKRERH]

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

Hi,

For week-ends on Fridays and Saturdays, you can use 7 as the argument in the function Networkdays.intl ... :wink:

HTH

What will be the new formula then?

 

[James006]

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

Well ...

Just replace Networkdays ... by Networkdays.intl and add the argument 7 ... :wink:

 

[KUKRERH]

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

Well ...

Just replace Networkdays ... by Networkdays.intl and add the argument 7 ... :wink:

I just updated the below formula. However not able to get any answer:-
=(NETWORKDAYS.INTL(A1,B1,K$1:K$1)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B1,B1,K$1:K$1),MEDIAN(MOD(B1,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A1,A1,K$1:K$1)*MOD(A1,1),"17:30","8:30")

 

[barry houdini]

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

Hello KUKRERH,

As James says, you need to specify the Friday/Saturday "weekend" by using 7 as the third argument of NETWORKDAYS.INTL function, like this

=(NETWORKDAYS.INTL(A1,B1,7,K$1:K$1)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B1,B1,7,K$1:K$1),MEDIAN(MOD(B1,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A1,A1,7,K$1:K$1)*MOD(A1,1),"17:30","8:30")

Ths will calculate all hours worked between 08:30 and 17:30 assuming working days are Sunday to Thursday, assuming A1 contains start time/date and B1 contains end time/date and K1:K1 is your holiday range.

This formula works even if A1 and B1 times/dates are outside working hours, if that won't be the case you may be able to use a simpler formula

 

[James006]

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

@ Barry

Always extremely happy to read your posts ...

Great fan of yours (daddylonglegs ..) :wink:

Eager to know if your site is now available ...

Cheers

 

[YogiXL]

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

Hi James

Above formula is working fine when we are calculating for only one location, but i need to calculate TAT at a time for multiple locations where each location have different different Holidays there TAT should not be calculated for respective location Holiday but same need to be calculated for other location

Example : (Feb 2017 common holidays are 2nd & 4th Saturdays and Sundays)
Location A - Holiday 7th Feb (Tuesday)
Location B - Holiday 15th Feb (Wednesday)
Location C - Holiday 20th Feb (Monday)

I hope am clear about my requirement - pls let me know if any further details required

Rgds
Yogi

 

[James006]

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

Hi,

If I understand you correctly ... since Holidays are listed in range H1:H10 ...

You do need to replace this portion by your own Vlookup() function which will pick the Holiday which corresponds to the selected location ...

HTH

 

[YogiXL]

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

Hi

Unfortunetly i did not get any result...!!

Rgds
Yogi