Formula for Trun Around Time (TAT) For incident No Weekend

[Abhishek Shrivastav]

Hi All,

I need formula for Trun around Time (TAT) For Incident.

For Ex. Start Date Time : 25/2/2018 10:00 AM

End time : 28/2/2018 6:00 PM

Manual TAT calculation is :- 53:00

Working hours for Sat to thu is 7:00 Am to 10 Pm  & For Friday is 9:30 AM to 6:30 PM

So I need the Formula as per the Working hours and all days which calculate TAT.

So please help me thank you

[#2]

Hi,

With data structured like this

the formula could be

=NETWORKDAYS.INTL(F2+1,G2-1,"1111011")*9/24 +
  (INT(G2-1)-INT(F2+1)+1-NETWORKDAYS.INTL(F2+1,G2-1,"1111011"))*15/24 +
 (IF(WEEKDAY(F2,2)=5,$C$3,$C$2)-MOD(F2,1) +
 MOD(G2,1)-IF(WEEKDAY(G2,2)=5,$B$3,$B$2))

I added helper cells for working hours, in general could be hardcoded using TIME() function.

Sample is attached.

[#3]

@Sergei Baklan 

Could you please help me out to calculate TAT between particular time Windows. 

Shift Start Time EST	Shift End Time EST
Sunday 8:30 PM	Monday 5:30 AM
Monday 8:00 AM	Monday 5:00 PM
Monday 8:30 PM	Tuesday 5:30 AM
Tuesday 8:00 AM	Tuesday 5:00 PM
Tuesday 8:30 PM	Wednesday 5:30 AM
Wednesday 8:00 AM	Wednesday 5:00 PM
Wednesday 8:30 PM	Thursday 5:30 AM
Thursday 8:00 AM	Thursday 5:00 PM
Thursday 8:30 PM	Friday 5:30 AM
Friday 8:00 AM	Friday 5:00 PM

Order_Date	Completed_Date	Manual TAT (Hours)	TAT
7/26/19 4:30 AM	7/29/19 9:15 PM	28:45
7/26/19 3:46 PM	7/30/19 3:46 PM	36:00
7/26/19 9:05 PM	7/30/19 9:05 PM	37:05
7/29/19 3:18 PM	7/29/19 11:00 PM	4:12

[#4]

@Sergei Baklan is it possible to exclude a list of national holidays in the calculation of TAT?

E.g. 02/04/2021 - Good Friday

[#5]

@tyrelmiranda 

NETWORKDAYS.INTL() allows to define holidays as 4th parameter.

[#6]

@Sergei Baklan hi your formula has been incredibly useful! thank you . my only problem is when i use your formula for start time being the day where teh working shift is longer (12.5 hours) and the end time is the day of shorter hours (10.5 hours) my result is giving me 2 hours extra; example below 

start time (working hours 12.5 hours this day- so 10 hours 15 mins until end of day)

05/08/2021 08:15

end time (start of day is 6am so 1 hour and 39 minutes to completion so total time should be 11 hours 54 minutes but im getting 13 hours 54 minutes)

06/08/2021 07:39

my formula 

=NETWORKDAYS.INTL(L369+1,Y369-1,"1111011")*10.5/24+(INT(Y369-1)-INT(L369+1)+1-NETWORKDAYS.INTL(L369+1,Y369-1,"1111011"))*12.5/24 +(IF(WEEKDAY(L369,2)=5,Sheet2!$C$4,Sheet2!$C$3)-MOD(L369,1)+MOD(Y369,1)-IF(WEEKDAY(Y369,2)=5,Sheet2!$B$4,Sheet2!$B$3))