Conditional Format

[Fegal]

Hello, please help.. my brain has the dumb.
column H is when the certificate was last acheived, I is the expiry of that cert.
I would like column I to be coloured to represent time left to renew.
If we have from today >= 547 days to expiry colour green, between 548 and 720 colour amber, more than 720 to 810 Red, 810 + Black...

hope you can help

National Training record 1.xlsx
	H	I	J
23	Current	14/09/2025
24	17/06/2023
25	Current	17/12/2024
26	19/09/2022
27	Current	14/09/2025
28	17/06/2023
29	Current	29/12/2024
30	1/10/2022
31	Current	19/07/2024
32	21/04/2022
33	Current	6/02/2025
34	9/11/2022
35	Current	14/09/2025
36	17/06/2023
37	Current	15/04/2025
38	16/01/2023
39	Expired	30/05/2023
40	1/03/2021
National Training

Cell Formulas
Range	Formula
H23,H39,H37,H35,H33,H31,H29,H27,H25	H23	=IF(H24<(TODAY()-820),$D$7,$D$4)
I23,I25,I27,I29,I31,I33,I35,I37,I39	I23	=IF($H24="","",H24+820)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H23:H116	Cell Value	=$D$7	text	NO
I23:I24	Cell Value	>$G$20>=547	text	NO
G23:I76,J23:AS148,G77:H80,G81:I92,G93:H94,G95:I102,G103:H104,G105:I116,G117:H148	Cell Value	=$D$5	text	NO

 

[Fegal]

Actually i have that wrong. this hurts the brain.
If 1.5 years from expiry green.
if today is less than 6 months from expiry amber.
less than 3 months from expiry red
expired black...

 

[Snakehips]

@Fegal I think it is the fact that you have merged cells in I that is making this difficult.
Generally, try and avoid merged cells that impact upon formula outcome.
That said, I wonder if like below will help?
I have included a formula in F which illustrates how to get the difference in days. It is not necessary to keep that for the CF to work.
I have included example CF for Black and for Red. Hopefully Amber and Green can be added with revised day limits.

Book1
	F	G	H	I
23			Current	9/14/25
24	819.00		6/17/23
25			Current	12/17/24
26	548.00		9/19/22
27			Current	9/14/25
28	819.00		6/17/23
29			Current	12/29/24
30	560.00		10/1/22
31			Current	7/19/24
32	397.00		4/21/22
33			Current	9/7/23
34	81.00		6/9/21
35			Current	9/14/25
36	819.00		6/17/23
37			Current	4/15/25
38	667.00		1/16/23
39			Expired	5/30/23
40	-19.00		3/1/21
41
National Training

Cell Formulas
Range	Formula
I23,I25,I27,I29,I31,I33,I35,I37,I39	I23	=IF($H24="","",H24+820)
F23:F40	F23	=IF(ISNUMBER(H23),MAX(I23,I22)-TODAY(),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H23:H40	Expression	=AND(ISNUMBER(H24),MAX(I23,I24)-TODAY()<=0)	text	NO
H23:H40	Expression	=AND(ISNUMBER(H24),MAX(I23,I24)-TODAY()<=91)	text	NO

 

[davesexcel]

Similar, I like the isnumber() from previous answer, didn't think of it.

Book1
	H	I
23	current	September 15, 2023
24	June 17, 2021
25	current	September 17, 2023
26	June 19, 2021
27	current	September 23, 2023
28	June 25, 2021
29	current	December 29, 2024
30	October 1, 2022
31	expired	July 20, 2022
32	April 21, 2020
33	current	February 6, 2025
34	November 9, 2022
35	current	September 14, 2025
36	June 17, 2023
37	current	April 15, 2025
38	January 16, 2023
39	expired	April 3, 2023
40	January 3, 2021
Sheet1

Cell Formulas
Range	Formula
H23,H39,H37,H35,H33,H31,H29,H27,H25	H23	=IF(H24<(TODAY()-820),$B$7,$B$4)
I23,I25,I27,I29,I31,I33,I35,I37,I39	I23	=IF($H24="","",H24+820)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I23:I40	Expression	=AND(I23<>"",I23-TODAY()>180)	text	NO
I23:I40	Expression	=AND(I23<>"",I23-TODAY()>90,I23-TODAY()<=180)	text	NO
I23:I40	Expression	=AND(I23<>"",I23-TODAY()>0,I23-TODAY()<=90)	text	NO
I23:I40	Expression	=I23-TODAY()<=0	text	NO

 

[Fegal]

Thanks guys, really appreciate the help.

 

[Snakehips]

Good job @davesexcel was awake. I was not highlighting the correct column!