Mar 13 2018
04:48 AM
- last edited on
Jul 25 2018
11:20 AM
by
TechCommunityAP
Mar 13 2018
04:48 AM
- last edited on
Jul 25 2018
11:20 AM
by
TechCommunityAP
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
Mar 13 2018 06:14 AM
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.
Aug 08 2019 11:30 AM
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 |
Apr 08 2021 01:56 AM
@Sergei Baklan is it possible to exclude a list of national holidays in the calculation of TAT?
E.g. 02/04/2021 - Good Friday
Apr 08 2021 10:29 AM
NETWORKDAYS.INTL() allows to define holidays as 4th parameter.