I'm using the below formula to calculate duration of time within our business hours (L2 & K2) on days our company is open.
The issue we're having is that sometimes people need to work on a holiday and it returns a negative value. All I want to do
is modify this formula to return a value of zero if the result of the formula is negative. Right now I'm using another column and simple
if formula, but I know you can do it all-inclusive, I just can't remember how.
Thanks for any help
The issue we're having is that sometimes people need to work on a holiday and it returns a negative value. All I want to do
is modify this formula to return a value of zero if the result of the formula is negative. Right now I'm using another column and simple
if formula, but I know you can do it all-inclusive, I just can't remember how.
Excel Formula:
=IF(G2="","",(NETWORKDAYS(F2,G2,holidays!A:A)-1)*($L$1-$K$1) +IF(NETWORKDAYS(G2,G2),MEDIAN(MOD(G2,1),$L$1,$K$1),$L$1) -MEDIAN(NETWORKDAYS(F2,F2)*MOD(F2,1),$L$1,$K$1))
Thanks for any help