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

Excel - formulas data worksheet

Formulas & Functions

SergeiBaklan

Diamond Contributor

Mar 13, 2018

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.

TAT.xlsx12 KB

gs7488

Copper Contributor

Aug 06, 2021

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

Forum Discussion

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