 Thanks:  0 Likes:  0

# Thread: I Need a formula to calculate the Turn Around Time

1. ## I Need a formula to calculate the Turn Around Time

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  Reply With Quote

2. ## 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  Reply With Quote

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

Thank youuuuuuuuuuu so much

Formula is working for me

Thank you so much for the great help     Reply With Quote

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

How do I add Holiday list to the formula "NETWORKDAYS"  Reply With Quote

5. ## 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")  Reply With Quote

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

Thank you so much   Reply With Quote

7. ## Re: I Need a formual to calculate the Turn Around Time Originally Posted by barry houdini 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
It is working, thanks a ton...!!!  Reply With Quote

8. ## Re: I Need a formual to calculate the Turn Around Time Originally Posted by barry houdini 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
This is an good help,

I would like to know if we have date and time in a different coloumn eg A2 has start date and B2 start time and then C2 end date, D2 end time in 24 hour format.
can you please advise on this.  Reply With Quote

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

Hi Barry! Can you explain the formula for me please? Originally Posted by barry houdini 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  Reply With Quote

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

Hi,

The safest way out is to rely on Barry's formula in post # 5 and amend it with your own "Holidays" list ...i.e your second Saturday and your fourth Saturday and Sunday ...

HTH  Reply With Quote

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•