Color based on No. Days

[Johnboy28]

Can anyone help - Based on the Production Date and code and today() Date - Need a formula to work out the "No. Days" over and Under and enter the colour (Red, Amber & Green based on table
Happy for you to change the format of the "No.Days" for formula to work

Code	Colour	No. Days
72040	Red	37 Over
72040	Amber	37
72040	Green	35 Under
70559	Red	46 Over
70559	Amber	46
70559	Green	44 Under
73307	Red	35 Over
73307	Amber	35
73307	Green	33 Under
70059	Red	35 Over
70059	Amber	35
70059	Green	33 Under
	Production Date	Code	Colour
	14/05/24	72040	??
	15/05/24	72040
	20/05/24	73307
	21/05/24	70059
	21/05/24	73307
	20/05/24	72040
	22/05/24	70059
	22/05/24	73307
	21/05/24	72040
	23/05/24	70059
	22/05/24	72040

 

[awoohaw]

Are you asking for the values between the two ranges to be amber?

 

[awoohaw]

Also, which version of Excel are you actually using this formula in? You have many versons listed. I suggest you change that to the one you most frequently use, or you'll need to identify which version you use whenever you post questions. The later versions of excel, particularly 365 have much more efficient and helpful functions.

 

[awoohaw]

Sorry to be a pain - Are you able to amend formula based on the below table. I have added to Days Diff Range so I cover the gaps. I hope this makes sense. Thank you

Code	Colour	Days Diff	Days Diff
72040	Red	37	100
72040	Amber	37	37
72040	Green	0	36
70559	Red	46	100
70559	Amber	46	46
70559	Green	0	45
73307	Red	35	100
73307	Amber	35	35
73307	Green	0	34
70059	Red	35	100
70059	Amber	35	35
70059	Green	0	34
Today	31/05/2024
Production Date	Code	Days Diff	Colour
14/05/24	72040	17.00	#N/A​
15/05/24	72040	16.00
20/05/24	73307	11.00
21/05/24	70059	10.00
21/05/24	73307	10.00
20/05/24	72040	11.00
22/05/24	70059	9.00
22/05/24	73307	9.00
21/05/24	72040	10.00
23/05/24	70059	8.00
22/05/24	72040	9.00

I've looked at this again. all you did was seem to add an upper limit on the RED. What happens if the days go in excess of 100? Is there a 4th color you want? Also I'd really suggest, unless there is a compelling reason, to create a matrix for your "days difference" ranges instead of a flat file. You example does not give any idea of expectations, so please change the days (even if not always a likely scenario) so that you cover all the different combinations of code and days different.

 

[Johnboy28]

I've looked at this again. all you did was seem to add an upper limit on the RED. What happens if the days go in excess of 100? Is there a 4th color you want? Also I'd really suggest, unless there is a compelling reason, to create a matrix for your "days difference" ranges instead of a flat file. You example does not give any idea of expectations, so please change the days (even if not always a likely scenario) so that you cover all the different combinations of code and days different.

I have amended the range - The days will never go over 100, I have increase to 500.

Code	Colour	Days Diff	Days Diff
72040	Red	38	500
72040	Amber	37	37
72040	Green	0	36
70559	Red	47	500
70559	Amber	46	46
70559	Green	0	45
73307	Red	36	500
73307	Amber	35	35
73307	Green	0	34
70059	Red	36	500
70059	Amber	35	35
70059	Green	0	34
	Today	31/05/2024
Production Date	Code	Days Diff	Colour
14/05/24	72040	17.00	#N/A​
15/05/24	72040	16.00
20/05/24	73307	11.00
21/05/24	70059	10.00
21/05/24	73307	10.00
20/05/24	72040	11.00
22/05/24	70059	9.00
22/05/24	73307	9.00
21/05/24	72040	10.00
23/05/24	70059	8.00
22/05/24	72040	9.00

 

[awoohaw]

I have amended the range - The days will never go over 100, I have increase to 500.

Code	Colour	Days Diff	Days Diff
72040	Red	38	500
72040	Amber	37	37
72040	Green	0	36
70559	Red	47	500
70559	Amber	46	46
70559	Green	0	45
73307	Red	36	500
73307	Amber	35	35
73307	Green	0	34
70059	Red	36	500
70059	Amber	35	35
70059	Green	0	34
	Today	31/05/2024
Production Date	Code	Days Diff	Colour
14/05/24	72040	17.00	#N/A​
15/05/24	72040	16.00
20/05/24	73307	11.00
21/05/24	70059	10.00
21/05/24	73307	10.00
20/05/24	72040	11.00
22/05/24	70059	9.00
22/05/24	73307	9.00
21/05/24	72040	10.00
23/05/24	70059	8.00
22/05/24	72040	9.00

I still don't understand why you even want to do that? If the RED range starts at the middle number and you don't care how high the "high" number is why even have a high number?????

All of your Greens start at ZERO. the only number I think you need is the middle number. Which is what I put in the first solution i offered. Please show me an example of where this does not work?

 

[Johnboy28]

I cant get your first solution to work? Happy to use the below

=SWITCH(SIGN(($C$8-A11)-LOOKUP($B11,$A$2:$A$5,$B$2:$B$5)),-1,"Green",0,"Amber",1,"Red")

code	reference days
70059	35
70559	46
72040	37
73307	35
	Today	31/05/2024
Production Date	Code	Days Diff	Colour
14/05/24	72040	17.00	#N/A​
15/05/24	72040	16.00
20/05/24	73307	11.00
21/05/24	70059	10.00
21/05/24	73307	10.00
20/05/24	72040	11.00
22/05/24	70059	9.00
22/05/24	73307	9.00
21/05/24	72040	10.00
23/05/24	70059	8.00
22/05/24	72040	9.00

 

[awoohaw]

I'm using it again, here:
What kind of error are you getting? What cell? What formula is in that cell? What is the result of the formula?

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Code	Colour	No. Days			Code	Reference Days
2	70059	Amber	35	35		70059	35
3	70059	Green	0	34		70559	46
4	70059	Red	35	500		72040	37
5	70559	Amber	46	46		73307	35
6	70559	Green	0	45
7	70559	Red	46	500
8	72040	Amber	37	37
9	72040	Green	0	36
10	72040	Red	37	500
11	73307	Amber	35	35
12	73307	Green	0	34
13	73307	Red	35	500
14									Conditional	Conditional	Conditional
15		Today:	2024-06-25						Formatting	Formatting	Formatting
16									Rule	Rule	Rule
17		Production Date	Code	Days diff					Green	Amber	Red
18		2024-05-13	72040	43	Red				FALSE	FALSE	TRUE
19		2024-04-08	72040	78	Red				FALSE	FALSE	TRUE
20		2024-02-04	73307	142	Red				FALSE	FALSE	TRUE
21		2024-05-21	70059	35	Amber				FALSE	TRUE	FALSE
22		2024-04-24	73307	62	Red				FALSE	FALSE	TRUE
23		2024-04-23	72040	63	Red				FALSE	FALSE	TRUE
24		2024-05-31	70059	25	Green				TRUE	FALSE	FALSE
25		2024-05-30	73307	26	Green				TRUE	FALSE	FALSE
26		2024-04-13	72040	73	Red				FALSE	FALSE	TRUE
27		2024-05-25	70059	31	Green				TRUE	FALSE	FALSE
28		2024-03-28	72040	89	Red				FALSE	FALSE	TRUE
29		2024-02-08	72040	138	Red				FALSE	FALSE	TRUE
30		2024-04-28	72040	58	Red				FALSE	FALSE	TRUE
31		2024-04-15	73307	71	Red				FALSE	FALSE	TRUE
32		2024-05-10	70559	46	Amber				FALSE	TRUE	FALSE
33		2024-05-31	73307	25	Green				TRUE	FALSE	FALSE
34		2024-05-02	72040	54	Red				FALSE	FALSE	TRUE
35		2024-03-11	70059	106	Red				FALSE	FALSE	TRUE
36		2024-05-04	73307	52	Red				FALSE	FALSE	TRUE
37		2024-04-16	72040	70	Red				FALSE	FALSE	TRUE
38		2024-02-07	70059	139	Red				FALSE	FALSE	TRUE
39		2024-05-29	72040	27	Green				TRUE	FALSE	FALSE
40		2024-05-19	72040	37	Amber				FALSE	TRUE	FALSE
41		2024-02-09	72040	137	Red				FALSE	FALSE	TRUE
42		2024-05-31	73307	25	Green				TRUE	FALSE	FALSE
43		2024-05-08	70059	48	Red				FALSE	FALSE	TRUE
44		2024-02-12	73307	134	Red				FALSE	FALSE	TRUE
45		2024-05-06	72040	50	Red				FALSE	FALSE	TRUE
46		2024-04-20	70059	66	Red				FALSE	FALSE	TRUE
47		2024-05-21	73307	35	Amber				FALSE	TRUE	FALSE
48		2024-05-13	72040	43	Red				FALSE	FALSE	TRUE
49		2024-04-26	70059	60	Red				FALSE	FALSE	TRUE
50		2024-03-22	72040	95	Red				FALSE	FALSE	TRUE
51		2024-05-31	73307	25	Green				TRUE	FALSE	FALSE
52		2024-05-19	70059	37	Red				FALSE	FALSE	TRUE
53		2024-03-27	73307	90	Red				FALSE	FALSE	TRUE
Sheet4

Cell Formulas
Range	Formula
D18:D53	D18	=$C$15-B18
E18:E53	E18	=SWITCH(SIGN(($C$15-B18)-LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)),-1,"Green",0,"Amber",1,"Red")
I18:I53	I18	=($C$15-B18)<LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)
J18:J53	J18	=($C$15-B18)=LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)
K18:K53	K18	=($C$15-B18)>LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B18:B53	Expression	=($C$15-B18)>LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO
B18:B53	Expression	=($C$15-B18)=LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO
B18:B53	Expression	=($C$15-B18)<LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO

 

[Johnboy28]

I'm using it again, here:
What kind of error are you getting? What cell? What formula is in that cell? What is the result of the formula?

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Code	Colour	No. Days			Code	Reference Days
2	70059	Amber	35	35		70059	35
3	70059	Green	0	34		70559	46
4	70059	Red	35	500		72040	37
5	70559	Amber	46	46		73307	35
6	70559	Green	0	45
7	70559	Red	46	500
8	72040	Amber	37	37
9	72040	Green	0	36
10	72040	Red	37	500
11	73307	Amber	35	35
12	73307	Green	0	34
13	73307	Red	35	500
14									Conditional	Conditional	Conditional
15		Today:	2024-06-25						Formatting	Formatting	Formatting
16									Rule	Rule	Rule
17		Production Date	Code	Days diff					Green	Amber	Red
18		2024-05-13	72040	43	Red				FALSE	FALSE	TRUE
19		2024-04-08	72040	78	Red				FALSE	FALSE	TRUE
20		2024-02-04	73307	142	Red				FALSE	FALSE	TRUE
21		2024-05-21	70059	35	Amber				FALSE	TRUE	FALSE
22		2024-04-24	73307	62	Red				FALSE	FALSE	TRUE
23		2024-04-23	72040	63	Red				FALSE	FALSE	TRUE
24		2024-05-31	70059	25	Green				TRUE	FALSE	FALSE
25		2024-05-30	73307	26	Green				TRUE	FALSE	FALSE
26		2024-04-13	72040	73	Red				FALSE	FALSE	TRUE
27		2024-05-25	70059	31	Green				TRUE	FALSE	FALSE
28		2024-03-28	72040	89	Red				FALSE	FALSE	TRUE
29		2024-02-08	72040	138	Red				FALSE	FALSE	TRUE
30		2024-04-28	72040	58	Red				FALSE	FALSE	TRUE
31		2024-04-15	73307	71	Red				FALSE	FALSE	TRUE
32		2024-05-10	70559	46	Amber				FALSE	TRUE	FALSE
33		2024-05-31	73307	25	Green				TRUE	FALSE	FALSE
34		2024-05-02	72040	54	Red				FALSE	FALSE	TRUE
35		2024-03-11	70059	106	Red				FALSE	FALSE	TRUE
36		2024-05-04	73307	52	Red				FALSE	FALSE	TRUE
37		2024-04-16	72040	70	Red				FALSE	FALSE	TRUE
38		2024-02-07	70059	139	Red				FALSE	FALSE	TRUE
39		2024-05-29	72040	27	Green				TRUE	FALSE	FALSE
40		2024-05-19	72040	37	Amber				FALSE	TRUE	FALSE
41		2024-02-09	72040	137	Red				FALSE	FALSE	TRUE
42		2024-05-31	73307	25	Green				TRUE	FALSE	FALSE
43		2024-05-08	70059	48	Red				FALSE	FALSE	TRUE
44		2024-02-12	73307	134	Red				FALSE	FALSE	TRUE
45		2024-05-06	72040	50	Red				FALSE	FALSE	TRUE
46		2024-04-20	70059	66	Red				FALSE	FALSE	TRUE
47		2024-05-21	73307	35	Amber				FALSE	TRUE	FALSE
48		2024-05-13	72040	43	Red				FALSE	FALSE	TRUE
49		2024-04-26	70059	60	Red				FALSE	FALSE	TRUE
50		2024-03-22	72040	95	Red				FALSE	FALSE	TRUE
51		2024-05-31	73307	25	Green				TRUE	FALSE	FALSE
52		2024-05-19	70059	37	Red				FALSE	FALSE	TRUE
53		2024-03-27	73307	90	Red				FALSE	FALSE	TRUE
Sheet4

Cell Formulas
Range	Formula
D18:D53	D18	=$C$15-B18
E18:E53	E18	=SWITCH(SIGN(($C$15-B18)-LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)),-1,"Green",0,"Amber",1,"Red")
I18:I53	I18	=($C$15-B18)<LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)
J18:J53	J18	=($C$15-B18)=LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)
K18:K53	K18	=($C$15-B18)>LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B18:B53	Expression	=($C$15-B18)>LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO
B18:B53	Expression	=($C$15-B18)=LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO
B18:B53	Expression	=($C$15-B18)<LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO

..=SWITCH(SIGN(($C$15-B18)-LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)),-1,"Green",0,"Amber",1,"Red") I'm pasting your formula in Cell E18. Error is #N/A

Could it be my version of Excel? Office 365

 

[awoohaw]

I use 365.

do you have two dots in front of the equal sign in the formula, or is that a typo when you pasted the expression?

What value is in cell C15? - This should be the reference date (I used a fixed date to get calculations, but would probably want TODAY() in your production sheet.

Confirm it is a date value (Use the formatting tool and change to NUMBER, if it doesn't change to a numeric value it is TEXT, and you need to fix.)​

What value is in cell B18? - This should be the first production date in your production date column. (ensure it is a date value test the same as for C15.)
What value is in cell C18? - This should be the code number associated with your first production date. IT IS NUMERIC, not text (add decimals using decimal

formatting - if you don't get decimal zeros then the cell is not numeric).​

What values are in cells F2:F5? - These are the code numbers look up, these should be in ascending sequence and NUMERIC. (test the same as C18 suggested above)
What values are in cells G2:G5? - These are the threshold numbers returned by the lookup, these should also be NUMERIC. (test the same as C18 suggested above)

Why not try to paste the entire mini worksheet (click the "copy" icon in the top left corner of mini sheet) onto a fresh worksheet.
The formulas should work. But, the conditional formatting will not transfer over and you need to build that with the formulas in the "Cells With Conditional Formatting" portion of the mini sheet post.

 

[Johnboy28]

I use 365.

do you have two dots in front of the equal sign in the formula, or is that a typo when you pasted the expression?

What value is in cell C15? - This should be the reference date (I used a fixed date to get calculations, but would probably want TODAY() in your production sheet.

Confirm it is a date value (Use the formatting tool and change to NUMBER, if it doesn't change to a numeric value it is TEXT, and you need to fix.)​

What value is in cell B18? - This should be the first production date in your production date column. (ensure it is a date value test the same as for C15.)
What value is in cell C18? - This should be the code number associated with your first production date. IT IS NUMERIC, not text (add decimals using decimal

formatting - if you don't get decimal zeros then the cell is not numeric).​

What values are in cells F2:F5? - These are the code numbers look up, these should be in ascending sequence and NUMERIC. (test the same as C18 suggested above)
What values are in cells G2:G5? - These are the threshold numbers returned by the lookup, these should also be NUMERIC. (test the same as C18 suggested above)

Why not try to paste the entire mini worksheet (click the "copy" icon in the top left corner of mini sheet) onto a fresh worksheet.
The formulas should work. But, the conditional formatting will not transfer over and you need to build that with the formulas in the "Cells With Conditional Formatting" portion of the mini sheet post.

Thank for all your help. I copied the Mini worksheet you did and pasted into my sheet and it all worked. Not sure what I did wrong. All good now. Thanks again. Appreciate it.