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.

4 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.