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]

What do you want when the days difference falls into the gaps?

 

[awoohaw]

Assuming the "gaps" between low and mid are to be GREEN, try this:

Book1
	A	B	C	D	E	F	G	H
1	Code	Colour	No. Days		Code	Reference Days
2	70059	Amber	35		70059	35
3	70059	Green	33 Under		70559	46
4	70059	Red	35 Over		72040	37
5	70559	Amber	46		73307	35
6	70559	Green	44 Under
7	70559	Red	46 Over
8	72040	Amber	37
9	72040	Green	35 Under
10	72040	Red	37 Over
11	73307	Amber	35
12	73307	Green	33 Under
13	73307	Red	35 Over
14
15		Today:	2024-06-25
16						Rule	Rule	Rule
17		Production Date	Code	Days diff		Green	Amber	Red
18		2024-05-14	72040	42		FALSE	FALSE	TRUE
19		2024-05-15	72040	41		FALSE	FALSE	TRUE
20		2024-05-20	73307	36		FALSE	FALSE	TRUE
21		2024-05-21	70059	35		FALSE	TRUE	FALSE
22		2024-05-21	73307	35		FALSE	TRUE	FALSE
23		2024-05-20	72040	36		TRUE	FALSE	FALSE
24		2024-05-22	70059	34		TRUE	FALSE	FALSE
25		2024-05-22	73307	34		TRUE	FALSE	FALSE
26		2024-05-21	72040	35		TRUE	FALSE	FALSE
27		2024-05-23	70059	33		TRUE	FALSE	FALSE
28		2024-05-22	72040	34		TRUE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
F18:F28	F18	=($C$15-B18)<LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
G18:G28	G18	=($C$15-B18)=LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
H18:H28	H18	=($C$15-B18)>LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
D18:D28	D18	=$C$15-B18

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

 

[Johnboy28]

Assuming the "gaps" between low and mid are to be GREEN, try this:

Book1
	A	B	C	D	E	F	G	H
1	Code	Colour	No. Days		Code	Reference Days
2	70059	Amber	35		70059	35
3	70059	Green	33 Under		70559	46
4	70059	Red	35 Over		72040	37
5	70559	Amber	46		73307	35
6	70559	Green	44 Under
7	70559	Red	46 Over
8	72040	Amber	37
9	72040	Green	35 Under
10	72040	Red	37 Over
11	73307	Amber	35
12	73307	Green	33 Under
13	73307	Red	35 Over
14
15		Today:	2024-06-25
16						Rule	Rule	Rule
17		Production Date	Code	Days diff		Green	Amber	Red
18		2024-05-14	72040	42		FALSE	FALSE	TRUE
19		2024-05-15	72040	41		FALSE	FALSE	TRUE
20		2024-05-20	73307	36		FALSE	FALSE	TRUE
21		2024-05-21	70059	35		FALSE	TRUE	FALSE
22		2024-05-21	73307	35		FALSE	TRUE	FALSE
23		2024-05-20	72040	36		TRUE	FALSE	FALSE
24		2024-05-22	70059	34		TRUE	FALSE	FALSE
25		2024-05-22	73307	34		TRUE	FALSE	FALSE
26		2024-05-21	72040	35		TRUE	FALSE	FALSE
27		2024-05-23	70059	33		TRUE	FALSE	FALSE
28		2024-05-22	72040	34		TRUE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
F18:F28	F18	=($C$15-B18)<LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
G18:G28	G18	=($C$15-B18)=LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
H18:H28	H18	=($C$15-B18)>LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
D18:D28	D18	=$C$15-B18

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

Thank you.

Is it possible to just include colour as per below sample. I have included the number of dayss between today's date and production date.

Code	Colour	No. Days
72040	Red	37 Over
72040	Amber	37
72040	Green	35 Under
70559	Red	46 Over
70559	Amber	46
70559	Green	44Under
73307	Red	35 Over
73307	Amber	35
73307	Green	33 Under
70059	Red	35 Over
70059	Amber	35
70059	Green	33 Under
	today	30/05/2024
	Production Date	Code	No. Days	Colour
	14/05/24	72040	16	Green
	15/05/24	72040	15
	20/05/24	73307	10
	21/05/24	70059	9
	21/05/24	73307	9
	20/05/24	72040	10
	22/05/24	70059	8
	22/05/24	73307	8
	21/05/24	72040	9
	23/05/24	70059	7
	22/05/24	72040	8

 

[awoohaw]

are you asking to see the words of the color in text?

If so, then this:

Book1
	A	B	C	D	E	F	G	H
1	Code	Colour	No. Days		Code	Reference Days
2	70059	Amber	35		70059	35
3	70059	Green	33 Under		70559	46
4	70059	Red	35 Over		72040	37
5	70559	Amber	46		73307	35
6	70559	Green	44 Under
7	70559	Red	46 Over
8	72040	Amber	37
9	72040	Green	35 Under
10	72040	Red	37 Over
11	73307	Amber	35
12	73307	Green	33 Under
13	73307	Red	35 Over
14
15		Today:	45468
16					Color Calc	Rule	Rule	Rule
17		Production Date	Code	Days diff		Green	Amber	Red
18		2024-05-14	72040	42	Red	FALSE	FALSE	TRUE
19		2024-05-15	72040	41	Red	FALSE	FALSE	TRUE
20		2024-05-20	73307	36	Red	FALSE	FALSE	TRUE
21		2024-05-21	70059	35	Amber	FALSE	TRUE	FALSE
22		2024-05-21	73307	35	Amber	FALSE	TRUE	FALSE
23		2024-05-20	72040	36	Green	TRUE	FALSE	FALSE
24		2024-05-22	70059	34	Green	TRUE	FALSE	FALSE
25		2024-05-22	73307	34	Green	TRUE	FALSE	FALSE
26		2024-05-21	72040	35	Green	TRUE	FALSE	FALSE
27		2024-05-23	70059	33	Green	TRUE	FALSE	FALSE
28		2024-05-22	72040	34	Green	TRUE	FALSE	FALSE
Sheet3

Cell Formulas
Range	Formula
D18:D28	D18	=$C$15-B18
E18:E28	E18	=SWITCH(SIGN(($C$15-B18)-LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)),-1,"Green",0,"Amber",1,"Red")
F18:F28	F18	=($C$15-B18)<LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
G18:G28	G18	=($C$15-B18)=LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
H18:H28	H18	=($C$15-B18)>LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)

 

[Johnboy28]

are you asking to see the words of the color in text?

If so, then this:

Book1
	A	B	C	D	E	F	G	H
1	Code	Colour	No. Days		Code	Reference Days
2	70059	Amber	35		70059	35
3	70059	Green	33 Under		70559	46
4	70059	Red	35 Over		72040	37
5	70559	Amber	46		73307	35
6	70559	Green	44 Under
7	70559	Red	46 Over
8	72040	Amber	37
9	72040	Green	35 Under
10	72040	Red	37 Over
11	73307	Amber	35
12	73307	Green	33 Under
13	73307	Red	35 Over
14
15		Today:	45468
16					Color Calc	Rule	Rule	Rule
17		Production Date	Code	Days diff		Green	Amber	Red
18		2024-05-14	72040	42	Red	FALSE	FALSE	TRUE
19		2024-05-15	72040	41	Red	FALSE	FALSE	TRUE
20		2024-05-20	73307	36	Red	FALSE	FALSE	TRUE
21		2024-05-21	70059	35	Amber	FALSE	TRUE	FALSE
22		2024-05-21	73307	35	Amber	FALSE	TRUE	FALSE
23		2024-05-20	72040	36	Green	TRUE	FALSE	FALSE
24		2024-05-22	70059	34	Green	TRUE	FALSE	FALSE
25		2024-05-22	73307	34	Green	TRUE	FALSE	FALSE
26		2024-05-21	72040	35	Green	TRUE	FALSE	FALSE
27		2024-05-23	70059	33	Green	TRUE	FALSE	FALSE
28		2024-05-22	72040	34	Green	TRUE	FALSE	FALSE
Sheet3

Cell Formulas
Range	Formula
D18:D28	D18	=$C$15-B18
E18:E28	E18	=SWITCH(SIGN(($C$15-B18)-LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)),-1,"Green",0,"Amber",1,"Red")
F18:F28	F18	=($C$15-B18)<LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
G18:G28	G18	=($C$15-B18)=LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)
H18:H28	H18	=($C$15-B18)>LOOKUP($C18,$E$2:$E$5,$F$2:$F$5)

Perfect - Thank you

 

[awoohaw]

Happy to help. But, I'm still not entirely sure I answered the exact question as there are gaps in your range of values that were not considered. I did not account for that. I asked about this and there was no answer. For example what happens in a scenario with days of 36 and Code: 72040?

Best Wishes.

 

[Johnboy28]

I have made a mistake on the days. I will adjust so the days are covered. Thanks again for your help.

 

[awoohaw]

Great, happy to help.

 

[Johnboy28]

Happy to help. But, I'm still not entirely sure I answered the exact question as there are gaps in your range of values that were not considered. I did not account for that. I asked about this and there was no answer. For example what happens in a scenario with days of 36 and Code: 72040?

Best Wishes.

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