Using IF/Then statements and Conditional Formatting

[ehall70]

I want to create a conditional rule on the following table that will color column E in the following ways:

If Column D is <= TODAY = Green
If Columns D is >TODAY < TODAY +14 = Yellow
If Column D is >TODAY +14 = Red

I am not sure how to do this and I need to be apply to multiple worksheets. The cell in column E should follow the rules even if it is blank. I worked it out once the dates are filled in, but would like to do it even if they are blank

Collect Updates From Business (C)	D	E
Expected Start Date	Expected End Date	Rec'd From Owner
12/16/2022​	1/20/2023​
12/16/2022​	2/3/2023​
12/16/2022​	1/20/2023​
12/16/2022​	1/20/2023​	01/19/23​
12/16/2022​	2/3/2023​
12/16/2022​	2/3/2023​
12/16/2022​	2/17/2023​
12/16/2022​	1/20/2023​
12/16/2022​	2/17/2023​
12/16/2022​	2/17/2023​

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E
1			Expected Start Date	Expected End Date	Rec'd From Owner
2			16/12/2022	20/01/2023
3			16/12/2022	03/02/2023
4			16/12/2022	20/01/2023
5			16/12/2022	20/01/2023	19/01/2023
6			16/12/2022	03/02/2023
7			16/12/2022	03/02/2023
8			16/12/2022	17/02/2023
9			16/12/2022	20/01/2023
10			16/12/2022	17/02/2023
11			16/12/2022	17/02/2023
12
13
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E:E	Expression	=AND(ISNUMBER(D1),D1>TODAY()+14)	text	NO
E:E	Expression	=AND(D1>TODAY(),D1<=TODAY()+14)	text	NO
E:E	Expression	=AND(D1<=TODAY(),D1<>"")	text	NO

 

[ehall70]

very close I think. In the scenario I envison, all cells in E would currently be green since non of those dates meet the rule of > D+14 or more

 

[Fluff]

I would E6 be green, the date in D6 is greeter than today, but less the Today+14

 

[ehall70]

Today is not greater than 03Feb2023. To be yellow it would be 04Feb23 - 17Feb23 and red would be anything beyond 18Feb23

 

[Fluff]

You originally said

If Columns D is >TODAY < TODAY +14 = Yellow

and that applies to D6

 

[ehall70]

sorry, I meant column E

You originally said

and that applies to D6

 

[Fluff]

In that case just change the D1 to E1 in all the formulae

 

[ehall70]

In that case just change the D1 to E1 in all the formulae

thanks!

 

[Fluff]

You're welcome.