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

Occasional Visitor

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

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.

5 Replies

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

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.

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

 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

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

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

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

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

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

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

@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))