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

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

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

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

How do I add Holiday list to the formula "NETWORKDAYS"

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

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

Thank you so much

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...!!!

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.

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

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

## 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
•