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
 
Hi All,

Just new here in MrExcel. I just need help in amending the above formula with the parameters below:
B2: Date and Time Received (14 Feb 2018 5:45 PM)
C2: Date and Time Responded (15 Feb 2018 12:30 AM)

Where...
(1) working hours is from 9:00 AM to 1:00 AM (Next Day)
(2) 15 Feb 2018 is a Public Holiday
(3) Formula should only calculate the Turnaround time without Public holidays and weekends.

Can someone kindly help me with this?

Will greatly appreciate your help :)
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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")

Hi Barry,

It does work. Could you please explain me the above one for my understanding like why have we chosen -1 in networkdays()/ Median, Mod.



Thanks again. You are doing a great job by helping people.
 
Last edited by a moderator:
Upvote 0
Hi,

I need to calculate if the order received by mu customer is dispatched on time or we are missing our TAT

Condition:

1) If the x type of order received from a local customer till 11 am that should be dispatch by the 3:30 PM
2) If the x type of order received from a local customer after 11 am and before 7 pm that should be dispatch the next day at 9:30 AM
3) If the Y type of order received from a local customer by 2:30 Pm that should be dispatch by the 3:30 PM
4) If the Y type of order received from a local customer after 2:30 pm and before 7 pm that should be dispatch the next day at 9:30 AM
5) If the Z type of order received from a local customer by 5:30 Pm that should be dispatch by the next day 9:30 AM
6) If the S type of order received from a local customer after 5:30 pm and before 7 pm that should be dispatch the next day at 3:30 PM
7) If the x type of order received from an outstation customer till 11:30 am that should be dispatch by the 4:00 PM
8) If the x type of order received from an outstation customer after 11:30 am and before 7 pm that should be dispatch the next day at 4:00 PM
9) If the Y type of order received from an outstation customer by 3:00 Pm that should be dispatch by the 4:00 PM
10) If the Y type of order received from an outstation customer after 3:00 pm and before 7 pm that should be dispatch the next day at 4:00 PM
11) If the Z type of order received from an outstation customer by 7:00 Pm that should be dispatch by the next day 4:00 PM
12) In above all type of orders (X, Y, and Z) if that order also belongs to condition A and or B automatically TAT increase for 1 day extra
13) Our delivery is closed on Sunday so if any order supposed to be delivered on Sunday for that order delivery time should be on Monday


suprol_order_numberorder_datetimecityOrder_TypeOrder_Type_2dispatch_timestamp
100450972​
24-06-21​
9:39​
OutstationXA
100450973​
24-06-21​
9:39​
OutstationZB
100450974​
24-06-21​
9:36​
LocalZ2021-06-25T08:51:23
100450975​
24-06-21​
9:36​
LocalX2021-06-24T23:17:58
100450976​
24-06-21​
9:36​
LocalZ2021-06-25T04:15:33
100450977​
24-06-21​
9:33​
OutstationZ
100450978​
24-06-21​
9:33​
OutstationZ
100450980​
24-06-21​
9:42​
OutstationZ
100450981​
24-06-21​
9:42​
LocalZ2021-06-25T08:51:44
100450982​
24-06-21​
9:42​
LocalX2021-06-24T23:15:17
100450983​
24-06-21​
9:42​
LocalX2021-06-24T23:17:59
100450984​
24-06-21​
9:45​
OutstationXA2021-06-24T17:28:05
100450986​
24-06-21​
9:45​
OutstationXB
100450987​
24-06-21​
9:48​
OutstationX
100450988​
24-06-21​
9:51​
OutstationY2021-06-24T13:00:59
100450989​
24-06-21​
10:00​
OutstationY2021-06-24T12:59:06
100450990​
24-06-21​
10:00​
OutstationX
100450993​
24-06-21​
10:00​
OutstationZ
100450994​
24-06-21​
10:00​
OutstationZ
100450995​
24-06-21​
10:00​
OutstationY2021-06-24T13:01:17
100450996​
24-06-21​
10:00​
OutstationZ
100450997​
24-06-21​
10:00​
OutstationZ
100450998​
24-06-21​
10:03​
OutstationZ
100450999​
24-06-21​
10:03​
OutstationXA2021-06-24T16:57:50
100451000​
24-06-21​
10:12​
OutstationYB2021-06-24T13:00:51
100451001​
24-06-21​
10:12​
OutstationX
100451002​
24-06-21​
10:12​
OutstationZ2021-06-25T08:52:25
100451003​
24-06-21​
10:21​
OutstationX2021-06-24T17:28:07
100451004​
24-06-21​
10:21​
OutstationY2021-06-24T13:00:57
100451005​
24-06-21​
10:24​
OutstationZ
100451007​
24-06-21​
10:24​
OutstationY2021-06-24T16:00:05
100451008​
24-06-21​
13:45​
OutstationZ
100451009​
24-06-21​
13:45​
OutstationZ
100451010​
24-06-21​
13:45​
OutstationZ
100451011​
24-06-21​
13:45​
OutstationZ
100451012​
24-06-21​
10:33​
OutstationX
100451015​
24-06-21​
10:33​
LocalZ2021-06-25T08:51:32
100451017​
24-06-21​
19:15​
LocalX2021-06-25T08:51:28
100451018​
24-06-21​
10:36​
OutstationYB
100451019​
24-06-21​
10:39​
OutstationX2021-06-24T17:38:19
100451020​
24-06-21​
10:39​
OutstationX
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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