Forum Discussion

Copper Contributor

Mar 13, 2018

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.