I Need a formula to calculate the Turn Around Time

hairajeshm

New Member
Joined
Dec 8, 2010
Messages
10
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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:):):)
 
Upvote 0
Re: I Need a formual to calculate the Turn Around Time

How do I add Holiday list to the formula "NETWORKDAYS"
 
Upvote 0
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")
 
Upvote 0
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

It is working, thanks a ton...!!!
 
Upvote 0
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

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.

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

Hi Barry! Can you explain the formula for me please?


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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top