If Or Statements (XP)

Topic

New Reply

Having difficulty with the logic of an IF OR statement. In H1 I have 8:30. In I1 I have 7:30. In column F I have this formula =IF(E4>$H$1,E4-$H$1,””) which calculates the difference between time worked and the standard work day (H1). This formula will enter any overtime. If none, it returns a blank cell. However because of my schedule i have some days which are non-standard (I1). What I want to be able to do in column F is write a formula which will calculate any overtime, be it a standard work day or a short day. What I tried was =IF(OR(E4>$H$1,E4-$H1,E4>$I$1,E4-$I$1)). This does not work. What logic am I missing?

Reply | Quote

Replies

You’ll have to indicate whether a day is to be compared to a standard day or to a short day, otherwise each time you work less than a standard day, time worked would automatically be compared with a short day, which is not what you want, I suspect.
Let’s say that you enter an “x” in column F, next to time worked, if it is a short day.
The formula would become

=IF(F4="x",IF(E4>$I$1,E4-$I$1,""),IF(E4>$H$4,E4-$H$4,""))

or

=IF(E4>IF(F4="x",$I$1,$H$1),E4-IF(F4="x",$I$1,$H$1),"")

Reply | Quote

Thanks.

Reply | Quote