conditional formatting problem with time past midnight

[jv3000]

Hi situation
I made a conditional format so that a cell in column F turns into a colour when the data of the cell in column E is in between the data in cell F14 and G14.
what I want to achieve is for example: 23:45 counts in between 23:00 and 03:00 BUT 22:00 not.

data between.xlsx
	E	F	G	H	I	J	K
9		X22
10		SCHEDULE
11		6345
12		36/37		IP		38
13		release	clean	release	clean	release	clean
14		23:00	3:00
15	21:45
16	22:00
17	22:15
18	22:30
19	22:45
20	23:00
21	23:15
22	23:30
23	23:45
24	0:00
25	0:15
26	0:30
27	0:45
28	1:00
29	1:15
30	1:30
31	1:45
32	2:00
33	2:15
34	2:30
35	2:45
36	3:00
37	3:15
38	3:30
39	3:45
40	4:00
41	4:15
42	4:30
43	4:45
44	5:00
nacht shift

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F44:G44	Expression	=IF(E44>=F14;E44<G14)	text	NO
F43:G43	Expression	=IF(E43>=F14;E43<G14)	text	NO
F42:G42	Expression	=IF(E42>=F14;E42<G14)	text	NO
F41:G41	Expression	=IF(E41>=F14;E41<G14)	text	NO
F40:G40	Expression	=IF(E40>=F14;E40<G14)	text	NO
F39:G39	Expression	=IF(E39>=F14;E39<G14)	text	NO
F38:G38	Expression	=IF(E38>=F14;E38<G14)	text	NO
F37:G37	Expression	=IF(E37>=F14;E37<G14)	text	NO
F36:G36	Expression	=IF(E36>=F14;E36<G14)	text	NO
F35:G35	Expression	=IF(E35>=F14;E35<G14)	text	NO
F34:G34	Expression	=IF(E34>=F14;E34<G14)	text	NO
F33:G33	Expression	=IF(E33>=F14;E33<G14)	text	NO
F32:G32	Expression	=IF(E32>=F14;E32<G14)	text	NO
F31:G31	Expression	=IF(E31>=F14;E31<G14)	text	NO
F30:G30	Expression	=IF(E30>=F14;E30<G14)	text	NO
F29:G29	Expression	=IF(E29>=F14;E29<G14)	text	NO
F28:G28	Expression	=IF(E28>=F14;E28<G14)	text	NO
F27:G27	Expression	=IF(E27>=F14;E27<G14)	text	NO
F26:G49	Expression	=IF(E26>=F14;E26<G14)	text	NO
F25:G25	Expression	=IF(E25>=F14;E25<G14)	text	NO
F24:G24	Expression	=IF(E24>=F14;E24<G14)	text	NO
F23:G23	Expression	=IF(E23>=F14;E23<G14)	text	NO
F22:G22	Expression	=IF(E22>=F14;E22<G14)	text	NO
F21:G21	Expression	=IF(E21>=F14;E21<G14)	text	NO
F20:G20	Expression	=IF(E20>=F14;E20<G14)	text	NO
F19:G19	Expression	=IF(E19>=F14;E19<G14)	text	NO
F18:G18	Expression	=IF(E18>=F14;E18<G14)	text	NO
F17:G17	Expression	=IF(E17>=F14;E17<G14)	text	NO
F16:G16	Expression	=IF(E16>=F14;E16<G14)	text	NO
F15:G15	Expression	=IF(E15>=F14;E15<G14)	text	NO
J44:K44	Expression	=IF(E44>=J14;E44<K14)	text	NO
J43:K43	Expression	=IF(E43>=J14;E43<K14)	text	NO
J42:K42	Expression	=IF(E42>=J14;E42<K14)	text	NO
J41:K41	Expression	=IF(E41>=J14;E41<K14)	text	NO
J40:K40	Expression	=IF(E40>=J14;E40<K14)	text	NO
J39:K39	Expression	=IF(E39>=J14;E39<K14)	text	NO
J38:K38	Expression	=IF(E38>=J14;E38<K14)	text	NO
J37:K37	Expression	=IF(E37>=J14;E37<K14)	text	NO
J36:K36	Expression	=IF(E36>=J14;E36<K14)	text	NO
J35:K35	Expression	=IF(E35>=J14;E35<K14)	text	NO
J34:K34	Expression	=IF(E34>=J14;E34<K14)	text	NO
J33:K33	Expression	=IF(E33>=J14;E33<K14)	text	NO
J32:K32	Expression	=IF(E32>=J14;E32<K14)	text	NO
J31:K31	Expression	=IF(E31>=J14;E31<K14)	text	NO
J30:K30	Expression	=IF(E30>=J14;E30<K14)	text	NO
J29:K29	Expression	=IF(E29>=J14;E29<K14)	text	NO
J28:K28	Expression	=IF(E28>=J14;E28<K14)	text	NO
J27:K27	Expression	=IF(E27>=J14;E27<K14)	text	NO
J26:K49	Expression	=IF(E26>=J14;E26<K14)	text	NO
J25:K25	Expression	=IF(E25>=J14;E25<K14)	text	NO
J24:K24	Expression	=IF(E24>=J14;E24<K14)	text	NO
J23:K23	Expression	=IF(E23>=J14;E23<K14)	text	NO
J22:K22	Expression	=IF(E22>=J14;E22<K14)	text	NO
J21:K21	Expression	=IF(E21>=J14;E21<K14)	text	NO
J20:K20	Expression	=IF(E20>=J14;E20<K14)	text	NO
J19:K19	Expression	=IF(E19>=J14;E19<K14)	text	NO
J18:K18	Expression	=IF(E18>=J14;E18<K14)	text	NO
J17:K17	Expression	=IF(E17>=J14;E17<K14)	text	NO
J16:K16	Expression	=IF(E16>=J14;E16<K14)	text	NO
J15:K15	Expression	=IF(E15>=J14;E15<K14)	text	NO
H44:I44	Expression	=IF(E44>=H14;E44<I14)	text	NO
H43:I43	Expression	=IF(E43>=H14;E43<I14)	text	NO
H42:I42	Expression	=IF(E42>=H14;E42<I14)	text	NO
H41:I41	Expression	=IF(E41>=H14;E41<I14)	text	NO
H40:I40	Expression	=IF(E40>=H14;E40<I14)	text	NO
H39:I39	Expression	=IF(E39>=H14;E39<I14)	text	NO
H38:I38	Expression	=IF(E38>=H14;E38<I14)	text	NO
H37:I37	Expression	=IF(E37>=H14;E37<I14)	text	NO
H36:I36	Expression	=IF(E36>=H14;E36<I14)	text	NO
H35:I35	Expression	=IF(E35>=H14;E35<I14)	text	NO
H34:I34	Expression	=IF(E34>=H14;E34<I14)	text	NO
H33:I33	Expression	=IF(E33>=H14;E33<I14)	text	NO
H32:I32	Expression	=IF(E32>=H14;E32<I14)	text	NO
H31:I31	Expression	=IF(E31>=H14;E31<I14)	text	NO
H30:I30	Expression	=IF(E30>=H14;E30<I14)	text	NO
H29:I29	Expression	=IF(E29>=H14;E29<I14)	text	NO
H28:I28	Expression	=IF(E28>=H14;E28<I14)	text	NO
H27:I27	Expression	=IF(E27>=H14;E27<I14)	text	NO
H26:I49	Expression	=IF(E26>=H14;E26<I14)	text	NO
H25:I25	Expression	=IF(E25>=H14;E25<I14)	text	NO
H24:I24	Expression	=IF(E24>=H14;E24<I14)	text	NO
H23:I23	Expression	=IF(E23>=H14;E23<I14)	text	NO
H22:I22	Expression	=IF(E22>=H14;E22<I14)	text	NO
H21:I21	Expression	=IF(E21>=H14;E21<I14)	text	NO
H20:I20	Expression	=IF(E20>=H14;E20<I14)	text	NO
H19:I19	Expression	=IF(E19>=H14;E19<I14)	text	NO
H18:I18	Expression	=IF(E18>=H14;E18<I14)	text	NO
H17:I17	Expression	=IF(E17>=H14;E17<I14)	text	NO
H16:I16	Expression	=IF(E16>=H14;E16<I14)	text	NO
H15:I15	Expression	=IF(E15>=H14;E15<I14)	text	NO

 

[6StringJazzer]

Do you really need four different conditional formatting colors, and a different rule for every row? It would be far easier to use one rule for all rows. At very least you should use one rule for each color.

In this example I used bold text for all rows, so there is only one rule.

Also, I highly recommend you get rid of merged cells.

$scratch.xlsm
	A	B	C	D	E	F	G
1		X22
2		SCHEDULE
3		6345
4		36/37		IP		38
5		release	clean	release	clean	release	clean
6		23:00	3:00
7	21:45
8	22:00
9	22:15
10	22:30
11	22:45
12	23:00
13	23:15
14	23:30
15	23:45
16	0:00
17	0:15
18	0:30
19	0:45
20	1:00
21	1:15
22	1:30
23	1:45
24	2:00
25	2:15
26	2:30
27	2:45
28	3:00
29	3:15
30	3:30
31	3:45
32	4:00
33	4:15
34	4:30
35	4:45
36	5:00
Time Between

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A7:C36	Expression	=AND($A7>=$B$6,$A7<$C$6+IF($C$6<$B$6,1,0))	text	NO

 

[jv3000]

Do you really need four different conditional formatting colors, and a different rule for every row? It would be far easier to use one rule for all rows. At very least you should use one rule for each color.

In this example I used bold text for all rows, so there is only one rule.

Also, I highly recommend you get rid of merged cells.

$scratch.xlsm
	A	B	C	D	E	F	G
1		X22
2		SCHEDULE
3		6345
4		36/37		IP		38
5		release	clean	release	clean	release	clean
6		23:00	3:00
7	21:45
8	22:00
9	22:15
10	22:30
11	22:45
12	23:00
13	23:15
14	23:30
15	23:45
16	0:00
17	0:15
18	0:30
19	0:45
20	1:00
21	1:15
22	1:30
23	1:45
24	2:00
25	2:15
26	2:30
27	2:45
28	3:00
29	3:15
30	3:30
31	3:45
32	4:00
33	4:15
34	4:30
35	4:45
36	5:00
Time Between

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A7:C36	Expression	=AND($A7>=$B$6,$A7<$C$6+IF($C$6<$B$6,1,0))	text	NO

Hi, thanks for your answer! the 4 colors are necessary. I made a rule for every row because I didn't know better. In your example the time in collom 1 is highlighted, I want the colors to be highlighted

 

[6StringJazzer]

What determines the colors you are using in the original?

You will need a different conditional formatting rule for each color, based on the logic I provided.

 

[jv3000]

What determines the colors you are using in the original?

You will need a different conditional formatting rule for each color, based on the logic I provided.

Blue means released too early, green on time. Orange means the wave closed (clean) almost nearly too late, red means closed too late

 

[6StringJazzer]

That is not meaningful to me based on your data. What I was getting at was how you determine which color goes in which row.

It looks to me like what you are trying to do is take the light colors you have assigned in your original layout, and use CF to replace them with darker versions of these colors if the time falls in the desired interval.

You need four rules:

Blue fill

=AND($A15>=$B$6,$A15<$C$6+IF($C$6<$B$6,1,0))

Applies To F15:K15

Green Fill

=AND($A16>=$B$6,$A16<$C$6+IF($C$6<$B$6,1,0))

Applies To F16:K19

Orange Fill

=AND($A20>=$B$6,$A20<$C$6+IF($C$6<$B$6,1,0))

Applies To F20:K21

Red Fill

=AND($A22>=$B$6,$A22<$C$6+IF($C$6<$B$6,1,0))

Applies To F22:K44