conditional format??

[JOEE1979]

I'm seriously stuck on this one.
The column "N" is times my drivers are supposed to be at their cars.
The column is highlighted yellow, showing me how many more runs my drivers have for the day.
In N3 I want to put a number (for example 4) that's how many trucks I have ready to be sent out.
How would I be able to use the number in "N3" and it will take the yellow highlights and make them green letting me know that my shipments are safe until a certain time?
Keep in mind that I have a top section and a bottom section, but I need them both to work together.
Thanks

Weekday Bus Cycling - copy.xlsx
	N
1
2	Sign on
3
4	10:05
5	15:00
6	16:00
7	18:00
8
9	20:00
10
11
12	4
13
14
15	Sign on
16
17	11:35
18	12:02
19	12:20
20	12:25
21	16:00
22	17:00
23	18:00
24
25
26	12:00
27	16:00
28	19:00
29
30	5
31
32
Steeprock

Cell Formulas
Range	Formula
N12	N12	=COUNTIF(N4:N11,">="&$A$1)
N30	N30	=COUNTIF(N17:N29,">="&$A$1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D11,N4:N11,D17:D29,N17:N29	Cell Value	>MOD(NOW(),1)	text	NO

 

[awoohaw]

Do you mean you want to know how many time slots are available for the remainder of the day that can meet your remaining deliveries?
Will you be entering a time value somewhere to do the comparison, or do you want the system time?

How do you enter the time values in Cells N4:N12, etc? Just hh:mm or is a a complete date? Do you ever go across midnight to a new day?

 

[awoohaw]

what is the value in cell $A$1 that you are comparing your counts to?

 

[awoohaw]

okay. I had to develop this with a fixed time value instead of now. So you'll need to update cell references and the NOW() value as appropriate.
This will highlight all times after your time check value that where the count of those is >= to your count check in row 3.
Conversely it will highlight in RED ALL times in the column above the time check count that are a>= to your count check in row 3.

Book1
	N	O	P	Q
1	w NOW()	w Q3 for dev
2	Sign on	Sign on		time used for development
3		5		16:00
4	10:05:00	10:05:00
5	15:00:00	15:00:00
6	16:00:00	16:00:00
7	18:00:00	18:00:00
8
9	20:00:00	20:00:00
10
11
12	0	2
Sheet1

Cell Formulas
Range	Formula
N12	N12	=COUNTIF(N4:N11,">="&$A$1)
O12	O12	=COUNTIF(O4:O11,">="&$Q$3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O4:O11	Expression	=AND(SUM((--($Q$3<=$O$4:$O$11)))<$O$3,O4>=$Q$3)	text	NO
O4:O11	Expression	=AND(SUM((--($Q$3<=O4:O11)))>=N3,O4>=$Q$3)	text	NO

 

[JOEE1979]

Sorry, Let me explain it better.
$D$1 =MOD(NOW(),1)
What I want to do is have a number in Cell "A2". That number would be how many trucks are ready to hit the road, (ie 10)
I want a way to take the "10" lowest numbers that are Highlighted in yellow (they have a conditional format already to make them yellow) and make them green

Weekday Bus Cycling - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4	MDDS5115	SPLT	muwtf	6:10	6:25	8:55	DDS	98.677		1	MDDS5121	REL	muwtf	10:05	10:20	20:20	DDS	394.644
5	MDDS5113	SPLT	muwtf	5:55	6:10	9:50	DDS	125.874		2	MDDS5122	REL	muwtf	10:40	10:55	19:55	DDS	328.82
6	MDDS5101	REG	muwtf	2:25	2:40	10:50	DDS	300.625		3	MDDS5124	REL	muwtf	11:50	12:05	21:50	DDS	409.936
7	MDDS5107	REG	muwtf	4:50	5:05	11:10	DDS	222.347		4	MDDS5125	SPLT	muwtf	12:05	12:20	17:45	DDS	227.905
8	MDDS5118	SPLT	muwtf	6:55	7:10	11:30	DDS	137.672		5	MDDS5126	REL	muwtf	12:19	12:34	23:45	DDS	449.661
9	MDDS5102	REG	muwtf	2:25	2:40	12:05	DDS	442.896		6	MDDS5127	SPLT	muwtf	12:50	13:05	18:30	DDS	223.105
10	MDDS5112	REG	muwtf	5:40	5:55	12:25	DDS	227.914		7	MDDS5128	REL	muwtf	13:20	13:35	23:10	DDS	350.172
11	MDDS5106	REG	muwtf	4:25	4:40	13:05	DDS	330.075		8	MDDS5129	REL	muwtf	13:30	13:45	22:15	DDS	255.073
12	MDDS5109	REG	muwtf	4:55	5:10	13:15	DDS	267.443		9	MDDS5130	REL	muwtf	13:35	13:50	23:43	DDS	437.086
13	MDDS5103	REG	muwtf	3:25	3:40	13:20	DDS	420.212		10	MDDS5131	REL	muwtf	13:55	14:10	21:40	DDS	296.312
14	MDDS5104	REG	muwtf	4:05	4:20	13:50	DDS	412.598		11	MDDS5132	REL	muwtf	14:05	14:20	0:25	DDS	406.034
15	MDDS5105	REG	muwtf	4:20	4:35	13:55	DDS	318.807		12	MDDS5133	REL	muwtf	14:20	14:35	22:15	DDS	307.042
16	MDDS5114	REG	muwtf	6:00	6:15	14:10	DDS	213.239		13	MDDS5134	REL	muwtf	14:30	14:45	21:15	DDS	210.293
17	MDDS5108	REG	muwtf	4:55	5:10	14:20	DDS	420.212		14	MDDS5135	REL	muwtf	14:45	15:00	22:45	DDS	220.381	RPT
18	MDDS5110	REG	muwtf	5:05	5:20	14:30	DDS	284.54		15	MDDS5136	REL	muwtf	15:05	15:20	0:25	DDS	339.3
19	MDDS5111	REG	muwtf	5:40	5:55	15:20	DDS	374.472		16	MDDS5137	REL	muwtf	16:15	16:25	3:10	DDS	436.021
20	MDDS5116	REG	muwtf	6:20	6:35	18:10	DDS	428.417		17
21	MDDS5117	REG	muwtf	6:35	6:50	18:35	DDS	402.575		18
22	MDDS5119	REG	muwtf	7:05	7:20	18:55	DDS	427.629		19
23	MDDS5120	REG	muwtf	7:35	7:50	19:55	DDS	404.488		20
24										21	MDDS5123	REL	muwtf	10:45	2:00	20:35	DDS	349.439
25
26
27				3		20								17		17
Steeprock

Cell Formulas
Range	Formula
D27,P27,N27,F27	D27	=COUNTIF(D4:D26,">="&$D$1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P4:P26,F4:F26,P32:P69,F32:F69,P73:P1048576,F73:F1048576	Cell Value	>$D$1	text	NO
N4:N26,D4:D26,N32:N69,D32:D69,N73:N1048576,D73:D1048576	Cell Value	>$D$1	text	NO

 

[MARK858]

I want a way to take the "10" lowest numbers that are Highlighted in yellow (they have a conditional format already to make them yellow) and make them green

Can you not just use the built in conditional format function for bottom 10 items for this?

Book1.xlsb
	K	L	M	N	O
4	MDDS5121	REL	muwtf	10:05:00	10:20:00
5	MDDS5122	REL	muwtf	10:40:00	10:55:00
6	MDDS5124	REL	muwtf	11:50:00	12:05:00
7	MDDS5125	SPLT	muwtf	12:05:00	12:20:00
8	MDDS5126	REL	muwtf	12:19:00	12:34:00
9	MDDS5127	SPLT	muwtf	12:50:00	13:05:00
10	MDDS5128	REL	muwtf	13:20:00	13:35:00
11	MDDS5129	REL	muwtf	13:30:00	13:45:00
12	MDDS5130	REL	muwtf	13:35:00	13:50:00
13	MDDS5131	REL	muwtf	13:55:00	14:10:00
14	MDDS5132	REL	muwtf	14:05:00	14:20:00
15	MDDS5133	REL	muwtf	14:20:00	14:35:00
16	MDDS5134	REL	muwtf	14:30:00	14:45:00
17	MDDS5135	REL	muwtf	14:45:00	15:00:00
18	MDDS5136	REL	muwtf	15:05:00	15:20:00
19	MDDS5137	REL	muwtf	16:15:00	16:25:00
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N4:N19	Cell Value	top 10 bottom values	text	NO

 

[MARK858]

Or use the formula below

Book1.xlsb
	A
2	5
Sheet7

Book1.xlsb
	N
4	10:05:00
5	10:40:00
6	11:50:00
7	12:05:00
8	12:19:00
9	12:50:00
10	13:20:00
11	09:30:00
12	13:35:00
13	13:55:00
14	10:05:00
15	14:20:00
16	14:30:00
17	14:45:00
18	15:05:00
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N4:N19	Expression	=N4<=SMALL($N$4:$N$19,$A$2)	text	NO

 

[awoohaw]

will you ever have any values in the highlighted yellow column that is less than result of the formula in D1?

 

[JOEE1979]

will you ever have any values in the highlighted yellow column that is less than result of the formula in D1?

no

 

[JOEE1979]

Can you not just use the built in conditional format function for bottom 10 items for this?

View attachment 86641
View attachment 86644

Book1.xlsb
	K	L	M	N	O
4	MDDS5121	REL	muwtf	10:05:00	10:20:00
5	MDDS5122	REL	muwtf	10:40:00	10:55:00
6	MDDS5124	REL	muwtf	11:50:00	12:05:00
7	MDDS5125	SPLT	muwtf	12:05:00	12:20:00
8	MDDS5126	REL	muwtf	12:19:00	12:34:00
9	MDDS5127	SPLT	muwtf	12:50:00	13:05:00
10	MDDS5128	REL	muwtf	13:20:00	13:35:00
11	MDDS5129	REL	muwtf	13:30:00	13:45:00
12	MDDS5130	REL	muwtf	13:35:00	13:50:00
13	MDDS5131	REL	muwtf	13:55:00	14:10:00
14	MDDS5132	REL	muwtf	14:05:00	14:20:00
15	MDDS5133	REL	muwtf	14:20:00	14:35:00
16	MDDS5134	REL	muwtf	14:30:00	14:45:00
17	MDDS5135	REL	muwtf	14:45:00	15:00:00
18	MDDS5136	REL	muwtf	15:05:00	15:20:00
19	MDDS5137	REL	muwtf	16:15:00	16:25:00
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N4:N19	Cell Value	top 10 bottom values	text	NO

This will not work as the "10" is not a constant and will change depending on the # of trucks I have ready to go