condition based on time

[JOEE1979]

Is there a way to make a conditional format to turn column F green if it is equal or less than the 1st part of column E, and same with column G being equal or greater than the 2nd part of column E?
Thanks

Book1
	E	F	G
4	19:00 - 5:00	18:39
5	5:00 - 13:00	4:31	13:00
6	5:00 - 13:00	4:34	13:00
Sheet1

 

[6StringJazzer]

You should split column E into two columns. The way you are showing it, it's text, so you have to use text functions to extract the time. Not a good design practice. Here's your solution but it's ugly:

$scratch.xlsm
	E	F	G
4	19:00 - 5:00	18:39
5	5:00 - 13:00	4:31	13:00
6	5:00 - 13:00	4:34	13:00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:G6	Expression	=G4>=TIMEVALUE(LEFT($E4,FIND(" ",$E4)-1))	text	NO
F4:F6	Expression	=F4<=TIMEVALUE(LEFT($E4,FIND(" ",$E4)-1))	text	NO

New and improved design:

$scratch.xlsm
	E	F	G	H
8	19:00	5:00	18:39
9	5:00	13:00	4:31	13:00
10	5:00	13:00	4:34	13:00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H8:H10	Expression	=H8>=$F8	text	NO
G8:G10	Expression	=G8<=E8	text	NO