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

 

[blue_hya]

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

 

[Murali Polasi]

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.

 

[mittalraghav2011]

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_number	order_date	time	city	Order_Type	Order_Type_2	dispatch_timestamp
100450972​	24-06-21​	9:39​	Outstation	X	A
100450973​	24-06-21​	9:39​	Outstation	Z	B
100450974​	24-06-21​	9:36​	Local	Z		2021-06-25T08:51:23
100450975​	24-06-21​	9:36​	Local	X		2021-06-24T23:17:58
100450976​	24-06-21​	9:36​	Local	Z		2021-06-25T04:15:33
100450977​	24-06-21​	9:33​	Outstation	Z
100450978​	24-06-21​	9:33​	Outstation	Z
100450980​	24-06-21​	9:42​	Outstation	Z
100450981​	24-06-21​	9:42​	Local	Z		2021-06-25T08:51:44
100450982​	24-06-21​	9:42​	Local	X		2021-06-24T23:15:17
100450983​	24-06-21​	9:42​	Local	X		2021-06-24T23:17:59
100450984​	24-06-21​	9:45​	Outstation	X	A	2021-06-24T17:28:05
100450986​	24-06-21​	9:45​	Outstation	X	B
100450987​	24-06-21​	9:48​	Outstation	X
100450988​	24-06-21​	9:51​	Outstation	Y		2021-06-24T13:00:59
100450989​	24-06-21​	10:00​	Outstation	Y		2021-06-24T12:59:06
100450990​	24-06-21​	10:00​	Outstation	X
100450993​	24-06-21​	10:00​	Outstation	Z
100450994​	24-06-21​	10:00​	Outstation	Z
100450995​	24-06-21​	10:00​	Outstation	Y		2021-06-24T13:01:17
100450996​	24-06-21​	10:00​	Outstation	Z
100450997​	24-06-21​	10:00​	Outstation	Z
100450998​	24-06-21​	10:03​	Outstation	Z
100450999​	24-06-21​	10:03​	Outstation	X	A	2021-06-24T16:57:50
100451000​	24-06-21​	10:12​	Outstation	Y	B	2021-06-24T13:00:51
100451001​	24-06-21​	10:12​	Outstation	X
100451002​	24-06-21​	10:12​	Outstation	Z		2021-06-25T08:52:25
100451003​	24-06-21​	10:21​	Outstation	X		2021-06-24T17:28:07
100451004​	24-06-21​	10:21​	Outstation	Y		2021-06-24T13:00:57
100451005​	24-06-21​	10:24​	Outstation	Z
100451007​	24-06-21​	10:24​	Outstation	Y		2021-06-24T16:00:05
100451008​	24-06-21​	13:45​	Outstation	Z
100451009​	24-06-21​	13:45​	Outstation	Z
100451010​	24-06-21​	13:45​	Outstation	Z
100451011​	24-06-21​	13:45​	Outstation	Z
100451012​	24-06-21​	10:33​	Outstation	X
100451015​	24-06-21​	10:33​	Local	Z		2021-06-25T08:51:32
100451017​	24-06-21​	19:15​	Local	X		2021-06-25T08:51:28
100451018​	24-06-21​	10:36​	Outstation	Y	B
100451019​	24-06-21​	10:39​	Outstation	X		2021-06-24T17:38:19
100451020​	24-06-21​	10:39​	Outstation	X