I have the formula
=IF(AND(HOUR(D2)<=8,HOUR(D2)>=19), "OPEN", "OOH")
This should return the required text values "OPEN" or "OOH" depending if the hour falls within the noted range.
When trying in cell E2 of this sheet https://docs.google.com/spreadsheets/d/1xNFVHLnQGkRgZdLmejCyU0BByOPBY8NMoIYj6SkTFGY/edit#gid=431567503 it returns a text value but not always the correct one. What could be missing from this formula?
I have also tried swapping the range around without success
=IF(AND(HOUR(D2)>=19),HOUR(D2)<=8, "OPEN", "OOH")
Update: also tried with OR instead of AND but it does no better
=IF(OR(HOUR(C3) >=19, HOUR(C3) <=8), "OPEN", "OOH")
CodePudding user response:
Your conditions are a bit wrong. The IF
form is:
=IF(condition, value_if_true, value_if_false)
In your question, you say hour >=19 OR hour <= 8, so you should be using OR, as you do in your update. However, you switched the true and false values in your updated statement. Try:
=IF(OR(HOUR(C3) >=19, HOUR(C3) <=8), "OOH", "OPEN")
With 8:01, this returns "OOH"
With 9:01, this returns "OPEN"
With 18:01, this returns "OPEN"
With 19:01, this returns "OOH"
CodePudding user response:
Logic error. The thing can't be <=8 and >=19 at the same time.
If you reverse the signs to >8 and <19, you might get closer to what you want.