Is there a way to round this formula to nearest .5 decimal with the IF formula I have? I can get it working, but when "PAST" or "NO HRS" comes out of the formula, I then try to devide and multiple and get the #VALUE! error.
How can I round this and still get the "PAST" or "NO HRS" when the IF portion of the formula comes true?
=ROUND(IF(L$4<today(),"past",if(and(l$4>=$C$13,L$4<=$C$14),$Q22/$C$26,IF(AND(L$4>=$D$13,L$4<=$D$14),$Q30/$C$34,IF(AND(L$4>=$E$13,L$4<=$E$14),$Q38/$C$42,IF(AND(L$4>=$F$13,L$4<=$F$14),$Q38/$C$42,IF(AND(L$4>=$G$13,L$4<=$G$14),$Q46/$C$50,IF(AND(L$4>=$H$13,L$4<=$H$14),$Q54/$C$58,"NO HRS")))))))/0.5,0)*0.5
Thanks in advance,
Lorne</today(),"past",if(and(l$4>
How can I round this and still get the "PAST" or "NO HRS" when the IF portion of the formula comes true?
=ROUND(IF(L$4<today(),"past",if(and(l$4>=$C$13,L$4<=$C$14),$Q22/$C$26,IF(AND(L$4>=$D$13,L$4<=$D$14),$Q30/$C$34,IF(AND(L$4>=$E$13,L$4<=$E$14),$Q38/$C$42,IF(AND(L$4>=$F$13,L$4<=$F$14),$Q38/$C$42,IF(AND(L$4>=$G$13,L$4<=$G$14),$Q46/$C$50,IF(AND(L$4>=$H$13,L$4<=$H$14),$Q54/$C$58,"NO HRS")))))))/0.5,0)*0.5
Thanks in advance,
Lorne</today(),"past",if(and(l$4>