If the hour is >=19 or <=8 then return a value "OOH" if its not within that range return "OPEN"

user_15144361 0
Viewed 29

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")
google-sheets-formula if-statement time date datetime
3 Answers

if from 8 to 19 = OPEN then use this in E2:

=INDEX(IF((HOUR(C2:C)>=8)*(HOUR(C2:C)<19), "OPEN", "OOH"))

enter image description here

user_56326290

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"

user_150537900

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.

user_663630
Related