Formula works - convert to conditional format

[Luke777]

Hi All,

I'm experimenting with the following formula

=IF(AND(H15>H17,H15<I17),"YES","NO")

H15 contains the current time using

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

H17 and I17 contain two times.

The first formula gives me a yes/no depending on if the current time is between the two specified times.

If I want to then highlight the cell RED using conditional formatting if the answer is YES, how do I do that?

Basically I'm not sure how to transfer the logic of the formula I've used to something Conditional Formatting can understand correctly.

I've tried removing the IF and the AND in various combinations but it doesn't seem to work.

I'm sure the solution is simple, but Conditional Formatting has always been my weakness I'm afraid

Thanks!

 

[MARK858]

Maybe...

=AND(H15>H17,H15<I17)

in the Conditional format rule

Book1
	G	H	I	J
12				YES
13
14
15		1:39 PM
16
17		12:34	14:02
Sheet3

Cell Formulas
Range	Formula
J12	J12	=IF(AND(H15>H17,H15<I17),"YES","NO")
H15	H15	=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H15	Expression	=AND(H15>H17,H15<I17)	text	NO

Book1
	G	H	I	J
12				NO
13
14
15		1:40 PM
16
17		12:34	13:02
Sheet3

Cell Formulas
Range	Formula
J12	J12	=IF(AND(H15>H17,H15<I17),"YES","NO")
H15	H15	=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H15	Expression	=AND(H15>H17,H15<I17)	text	NO

 

[Luke777]

Maybe...

=AND(H15>H17,H15<I17)

in the Conditional format rule

Book1
	G	H	I	J
12				YES
13
14
15		1:39 PM
16
17		12:34	14:02
Sheet3

Cell Formulas
Range	Formula
J12	J12	=IF(AND(H15>H17,H15<I17),"YES","NO")
H15	H15	=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H15	Expression	=AND(H15>H17,H15<I17)	text	NO

Book1
	G	H	I	J
12				NO
13
14
15		1:40 PM
16
17		12:34	13:02
Sheet3

Cell Formulas
Range	Formula
J12	J12	=IF(AND(H15>H17,H15<I17),"YES","NO")
H15	H15	=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H15	Expression	=AND(H15>H17,H15<I17)	text	NO

Thanks for that

 

[MARK858]

You're welcome