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.
Aug 06 2021 02:05 AM
@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))
Nov 14 2023 03:13 AM
@Sergei Baklan I am working on a sheet where I need to calculate the turn-around time. I applied the network.intl formula and getting the number days we took for deliveries.
However, the number of days getting calculated is 2 days (and the enquiry days is 13th and we shared the resume on 14th). can you please help
Nov 15 2023 09:04 AM
NETWORKDAYS.INTL counts both start and end days, you need to deduct one. Perhaps you may share the sample what exactly and how you calculate.