CODEWITHSUNDEEP
exceldateif-statementexcel-formula
Kon Ath
Kon Ath

Reputation: 183

Use "if" function to identify specific hours

I have an excel table see attached picture. I want to find which cells (rows) contain specific hours. To do so I used the if function: 

=IF(B2=HOUR("12:00:00");"xx";"yes")

The outcome was not correct. As you can see the outcome was "yes" corresponding to false.. What is the problem ?

In addition, I tried to embed the "and" function, in order to select multiple hours, but the excel prompt error message. The formula I tried in cell B2 is: 

=IF(B2=HOUR(AND("12:00:00";"3:00:00");"xx";"yes"))

error message:

contain many conditions

Upvotes: 1

Views: 6798

Answers (2)

vincrichaud
vincrichaud

Reputation: 2208

First you just have misplaced the return of the true and the return of the false. Try :

=IF(B2=HOUR("12:00:00");"yes";"xx")

Second, you do not want a logical and but a logical or. And your condition is "if B2 is an hour that is equals to '12:00 and 3:00'" this has no sense. What you want is "if B2 is an hour that is equals to '12:00' or B2 is an hour that is equals to '3:00'". This should look like :

=IF(OR(B2=HOUR("12:00:00"), B2=HOUR("3:00:00")) ; "yes"; "xx"))

Edit : Thanks to Korrat answer I can correct mine. The hour should not be compared like this. You should use the function HOUR on the cell and not on the value.

=IF(OR(HOUR(B2)=12, HOUR(B2)=3) ; "yes"; "xx"))

Upvotes: 1

Korrat
Korrat

Reputation: 307

I think you have swapped the arguments in your comparison. According to the documentation

[HOUR] returns the hour of a time value

If we evaluate your formula step by step, we get:

HOUR("12:00:00") -> 12
B2 = 12 -> false
IF(false; "xx"; "yes") -> yes

To fix this, change your condition to HOUR(B2) = 12. That should work for a single hour.

If you want to check for multiple hour values, you can use the OR function, as noted in a previous answer, but with modified conditions like this:

IF(OR(HOUR(B2) = 3; HOUR(B2) = 12); "xx"; "yes")

Upvotes: 4

Related Questions