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

 

[Johnboy28]

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.

I noticed the formula is not working correctly. See below example

Code	Colour	Colour	Colour
72040	Red	37>	Amber	37	Green	36<
70559	Red	46>	Amber	46	Green	45<
73307	Red	35>	Amber	35	Green	34<
70059	Red	35>	Amber	35	Green	34<
Code	Reference Days
72040	37
70559	46
73307	35
70059	35
Today:	1/06/2024
Production Date	Code		Days diff	Color Calc
20/04/2024	72040		42	Green	Should be red over 37 days
25/04/2024	72040		37	Green	Should be Amber 37 days
14/05/2024	70559		18	Green	correct
14/04/2024	73307		48	Red	correct
14/05/2024	70059		18	#N/A	should be green

 

[awoohaw]

I'm sorry, the rules still work as my earlier post and get the results you say you expect.
Please do as I do and make calculation columns for each conditional format color to test your work.

Book1
	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-01						Formatting	Formatting	Formatting
16									Rule	Rule	Rule
17		Production Date	Code	Days diff					Green	Amber	Red
18		2024-05-13	72040	19	Green				TRUE	FALSE	FALSE
19		2024-04-08	72040	54	Red				FALSE	FALSE	TRUE
20		2024-02-04	73307	118	Red				FALSE	FALSE	TRUE
21		2024-05-21	70059	11	Green				TRUE	FALSE	FALSE
22		2024-04-24	73307	38	Red				FALSE	FALSE	TRUE
23		2024-04-23	72040	39	Red				FALSE	FALSE	TRUE
24		2024-05-31	70059	1	Green				TRUE	FALSE	FALSE
25		2024-05-30	73307	2	Green				TRUE	FALSE	FALSE
26		2024-04-13	72040	49	Red				FALSE	FALSE	TRUE
27		2024-05-25	70059	7	Green				TRUE	FALSE	FALSE
28		2024-03-28	72040	65	Red				FALSE	FALSE	TRUE
29		2024-02-08	72040	114	Red				FALSE	FALSE	TRUE
30		2024-04-28	72040	34	Green				TRUE	FALSE	FALSE
31		2024-04-15	73307	47	Red				FALSE	FALSE	TRUE
32		2024-05-10	70559	22	Green				TRUE	FALSE	FALSE
33		2024-05-31	73307	1	Green				TRUE	FALSE	FALSE
34		2024-05-02	72040	30	Green				TRUE	FALSE	FALSE
35		2024-03-11	70059	82	Red				FALSE	FALSE	TRUE
36		2024-05-04	73307	28	Green				TRUE	FALSE	FALSE
37		2024-04-16	72040	46	Red				FALSE	FALSE	TRUE
38		2024-02-07	70059	115	Red				FALSE	FALSE	TRUE
39		2024-05-29	72040	3	Green				TRUE	FALSE	FALSE
40		2024-05-19	72040	13	Green				TRUE	FALSE	FALSE
41		2024-02-09	72040	113	Red				FALSE	FALSE	TRUE
42		2024-05-31	73307	1	Green				TRUE	FALSE	FALSE
43		2024-05-08	70059	24	Green				TRUE	FALSE	FALSE
44		2024-02-12	73307	110	Red				FALSE	FALSE	TRUE
45		2024-05-06	72040	26	Green				TRUE	FALSE	FALSE
46		2024-04-20	70059	42	Red				FALSE	FALSE	TRUE
47		2024-05-21	73307	11	Green				TRUE	FALSE	FALSE
48		2024-05-13	72040	19	Green				TRUE	FALSE	FALSE
49		2024-04-26	70059	36	Red				FALSE	FALSE	TRUE
50		2024-03-22	72040	71	Red				FALSE	FALSE	TRUE
51		2024-05-31	73307	1	Green				TRUE	FALSE	FALSE
52		2024-05-19	70059	13	Green				TRUE	FALSE	FALSE
53		2024-03-27	73307	66	Red				FALSE	FALSE	TRUE
54
55
56											Expected Color
57		Production Date	Code					Days diff	Color Calc
58		2024-04-20	72040	42	Red			42	Green	Should be red over 37 days	Red
59		2024-04-25	72040	37	Amber			37	Green	Should be Amber 37 days	Amber
60		2024-05-14	70559	18	Green			18	Green	correct	Green
61		2024-04-14	73307	48	Red			48	Red	correct	Red
62		2024-05-14	70059	18	Green			18	#N/A	should be green	Green
Sheet3

Cell Formulas
Range	Formula
D18:D53,D58:D62	D18	=$C$15-B18
E18:E53,E58:E62	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,B58:B62	Expression	=($C$15-B18)>LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO
B18:B53,B58:B62	Expression	=($C$15-B18)=LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO
B18:B53,B58:B62	Expression	=($C$15-B18)<LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO

 

[Johnboy28]

I'm sorry, the rules still work as my earlier post and get the results you say you expect.
Please do as I do and make calculation columns for each conditional format color to test your work.

Book1
	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-01						Formatting	Formatting	Formatting
16									Rule	Rule	Rule
17		Production Date	Code	Days diff					Green	Amber	Red
18		2024-05-13	72040	19	Green				TRUE	FALSE	FALSE
19		2024-04-08	72040	54	Red				FALSE	FALSE	TRUE
20		2024-02-04	73307	118	Red				FALSE	FALSE	TRUE
21		2024-05-21	70059	11	Green				TRUE	FALSE	FALSE
22		2024-04-24	73307	38	Red				FALSE	FALSE	TRUE
23		2024-04-23	72040	39	Red				FALSE	FALSE	TRUE
24		2024-05-31	70059	1	Green				TRUE	FALSE	FALSE
25		2024-05-30	73307	2	Green				TRUE	FALSE	FALSE
26		2024-04-13	72040	49	Red				FALSE	FALSE	TRUE
27		2024-05-25	70059	7	Green				TRUE	FALSE	FALSE
28		2024-03-28	72040	65	Red				FALSE	FALSE	TRUE
29		2024-02-08	72040	114	Red				FALSE	FALSE	TRUE
30		2024-04-28	72040	34	Green				TRUE	FALSE	FALSE
31		2024-04-15	73307	47	Red				FALSE	FALSE	TRUE
32		2024-05-10	70559	22	Green				TRUE	FALSE	FALSE
33		2024-05-31	73307	1	Green				TRUE	FALSE	FALSE
34		2024-05-02	72040	30	Green				TRUE	FALSE	FALSE
35		2024-03-11	70059	82	Red				FALSE	FALSE	TRUE
36		2024-05-04	73307	28	Green				TRUE	FALSE	FALSE
37		2024-04-16	72040	46	Red				FALSE	FALSE	TRUE
38		2024-02-07	70059	115	Red				FALSE	FALSE	TRUE
39		2024-05-29	72040	3	Green				TRUE	FALSE	FALSE
40		2024-05-19	72040	13	Green				TRUE	FALSE	FALSE
41		2024-02-09	72040	113	Red				FALSE	FALSE	TRUE
42		2024-05-31	73307	1	Green				TRUE	FALSE	FALSE
43		2024-05-08	70059	24	Green				TRUE	FALSE	FALSE
44		2024-02-12	73307	110	Red				FALSE	FALSE	TRUE
45		2024-05-06	72040	26	Green				TRUE	FALSE	FALSE
46		2024-04-20	70059	42	Red				FALSE	FALSE	TRUE
47		2024-05-21	73307	11	Green				TRUE	FALSE	FALSE
48		2024-05-13	72040	19	Green				TRUE	FALSE	FALSE
49		2024-04-26	70059	36	Red				FALSE	FALSE	TRUE
50		2024-03-22	72040	71	Red				FALSE	FALSE	TRUE
51		2024-05-31	73307	1	Green				TRUE	FALSE	FALSE
52		2024-05-19	70059	13	Green				TRUE	FALSE	FALSE
53		2024-03-27	73307	66	Red				FALSE	FALSE	TRUE
54
55
56											Expected Color
57		Production Date	Code					Days diff	Color Calc
58		2024-04-20	72040	42	Red			42	Green	Should be red over 37 days	Red
59		2024-04-25	72040	37	Amber			37	Green	Should be Amber 37 days	Amber
60		2024-05-14	70559	18	Green			18	Green	correct	Green
61		2024-04-14	73307	48	Red			48	Red	correct	Red
62		2024-05-14	70059	18	Green			18	#N/A	should be green	Green
Sheet3

Cell Formulas
Range	Formula
D18:D53,D58:D62	D18	=$C$15-B18
E18:E53,E58:E62	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,B58:B62	Expression	=($C$15-B18)>LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO
B18:B53,B58:B62	Expression	=($C$15-B18)=LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO
B18:B53,B58:B62	Expression	=($C$15-B18)<LOOKUP($C18,$F$2:$F$5,$G$2:$G$5)	text	NO

Thank you . I have it all working now. Thank you so much for your help.