Forum Discussion
How to calculate Turn Around Time (TAT) considering weekend and working Hours..!
Assuming that's Excel and assuming Start/End could be outside the working hours, for such sample
formula in C2 could be
=IF(INT(B2)>INT(A2),
NETWORKDAYS.INTL(A2+1,B2-1,"0000011",$J$2:$J$3)*8/24 +
(WORKDAY.INTL(A2-1,1,"0000011",$J$2:$J$3)=INT(A2))*
(
$F$2-MEDIAN($E$2,$F$2,MOD(A2,1)) +
$H$2-MEDIAN($G$2,$H$2,MOD(A2,1))
) +
(WORKDAY.INTL(B2-1,1,"0000011",$J$2:$J$3)=INT(B2))*
(
MEDIAN($E$2,$F$2,MOD(B2,1))-$E$2 +
MEDIAN($G$2,$H$2,MOD(B2,1))-$G$2
),
MIN($F$2,MOD(B2,1))-MEDIAN($E$2,$F$2,MOD(A2,1))+
MEDIAN($G$2,$H$2,MOD(B2,1))-MAX($G$2,MOD(A2,1))
)Result formatted as [hh]:mm.
Firtst we check if Start/End dates are different days, if so calculate number of full workdays in between plus time for each of Start/End if only they are workdays. Finally calculate the difference if Start/End is the same date.
I am getting negative values which is resulting in '######' being displayed as below. Could you please check and advise what needs to be done.
Oops, too old formula. If to fix it, change in second line
NETWORKDAYS.INTL(...) on
MAX(0, NETWORKDAYS.INTL(...) )
Entire formula will be
=IF(INT(B2)>INT(A2), MAX( 0,NETWORKDAYS.INTL(A2+1,B2-1,"0000011",$J$2:$J$3))*8/24 + (WORKDAY.INTL(A2-1,1,"0000011",$J$2:$J$3)=INT(A2))* ( $F$2-MEDIAN($E$2,$F$2,MOD(A2,1)) + $H$2-MEDIAN($G$2,$H$2,MOD(A2,1)) ) + (WORKDAY.INTL(B2-1,1,"0000011",$J$2:$J$3)=INT(B2))* ( MEDIAN($E$2,$F$2,MOD(B2,1))-$E$2 + MEDIAN($G$2,$H$2,MOD(B2,1))-$G$2 ), MIN($F$2,MOD(B2,1))-MEDIAN($E$2,$F$2,MOD(A2,1))+ MEDIAN($G$2,$H$2,MOD(B2,1))-MAX($G$2,MOD(A2,1)) )which gives
SergeiBaklan this is so helpful. Thanks for posting! Would it be a way to include those outside working hours? The formula works great withing working hours 9:00 to 13:00 and 14:00 to 18:00, but for those outside working hours return negative numbers. Thanks so much
