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.