Conditional Formatting Cells based on date

[Cracklehands]

Hi,

I am struggling to find the correct formula to conditionally format the cell, based on the date within that cell. For example, if in Cell B5 I enter the date 22/11/2022, then i want the following conditioning to change the cell fill colour:
22/11/2022 plus up to 9 months - cell is green
22/11/2022 plus 9-12 months - cell is amber
22/11/2022 plus 12 months+ - cell is red.

This is so I can track certificate date renewals. Once the certificate is issued (22/11/2022) it is in date and therefore green. In 9 months time, it will change to amber to highlight that it is coming up for reneweal. In 12 months time I want it to change to red to show it is overdue.

 

[kevin9999]

I've added the CF to 2 more cells (B7 & B9) just to show the effect on earlier dates.

Book1
	A	B	C
3
4
5		22/11/2022
6
7		22/08/2022
8
9		22/05/2022
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9	Expression	=AND(B9<>"",TODAY()<EDATE(B9,9))	text	YES
B9	Expression	=AND(B9<>"",TODAY()>=EDATE(B9,9),TODAY()<EDATE(B9,12))	text	YES
B9	Expression	=AND(B9<>"",TODAY()>=EDATE(B9,12))	text	YES
B7	Expression	=AND(B7<>"",TODAY()<EDATE(B7,9))	text	YES
B7	Expression	=AND(B7<>"",TODAY()>=EDATE(B7,9),TODAY()<EDATE(B7,12))	text	YES
B7	Expression	=AND(B7<>"",TODAY()>=EDATE(B7,12))	text	YES
B5	Expression	=AND(B5<>"",TODAY()<EDATE(B5,9))	text	YES
B5	Expression	=AND(B5<>"",TODAY()>=EDATE(B5,9),TODAY()<EDATE(B5,12))	text	YES
B5	Expression	=AND(B5<>"",TODAY()>=EDATE(B5,12))	text	YES

 

[Cracklehands]

I've added the CF to 2 more cells (B7 & B9) just to show the effect on earlier dates.

Book1
	A	B	C
3
4
5		22/11/2022
6
7		22/08/2022
8
9		22/05/2022
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9	Expression	=AND(B9<>"",TODAY()<EDATE(B9,9))	text	YES
B9	Expression	=AND(B9<>"",TODAY()>=EDATE(B9,9),TODAY()<EDATE(B9,12))	text	YES
B9	Expression	=AND(B9<>"",TODAY()>=EDATE(B9,12))	text	YES
B7	Expression	=AND(B7<>"",TODAY()<EDATE(B7,9))	text	YES
B7	Expression	=AND(B7<>"",TODAY()>=EDATE(B7,9),TODAY()<EDATE(B7,12))	text	YES
B7	Expression	=AND(B7<>"",TODAY()>=EDATE(B7,12))	text	YES
B5	Expression	=AND(B5<>"",TODAY()<EDATE(B5,9))	text	YES
B5	Expression	=AND(B5<>"",TODAY()>=EDATE(B5,9),TODAY()<EDATE(B5,12))	text	YES
B5	Expression	=AND(B5<>"",TODAY()>=EDATE(B5,12))	text	YES

That worked great, thank you,

 

[kevin9999]

Happy to help, and welcome to the board