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​

 

[ehall70]

Are you trying to check the value in col E & if it's blank use cold D instead?

no, just looking at whether the date in column D has passed and by how far, and then shade column E appropriately. If it essentially a project management tool to see who I need to follow up with

 

[Fluff]

In that case is the mini-sheet in post#2 showing the correct colours?

 

[ehall70]

No. If we are using today's date I would expect the following:

Rows 1, 3 and 8 to be yellow and all the rest to be green. Today's date

Tomorrow, I would expect any row with 3Feb23 (rows 2,10,11) in column D to change to yellow

 

[Fluff]

But why col D on those rows is less than Today, which you said should be green

 

[ehall70]

But why col D on those rows is less than Today, which you said should be green

yes, you are correct. I got my dates messed up. Only those which were dated 20Jan23 would have a color at this point.

 

[Fluff]

But why?

 

[ehall70]

But why?

okay, let me slow down because I keep sending you messages that I am not putting all my attention toward. At this point I have only confused you.

The goal of the sheet is to let me know when a project is falling behind due to lack of data submission. I want column E to be shaded the colors indicated based on today's date relative to the date in column D. This way, at a glance, I can see who I need to follow up with to get the data.

Given that, rows 1, 3 and 8 should all be yellow because they are > the date in column D but < the date in column D + 14 days. On 4Feb23, I would expect those to turn red if they are still blank.

All other rows would be green since those dates in column D have not yet passed to trigger any of the rules. Once a date is entered in column E, no further changes to shading should occur as the data has now been collected. The cell should then stay the indicated color depending on the rules. For instance, let's say a date of 30Jan23 is entered in cell E1, that cell should already be yellow and would now stay that way going forward.

I hope that helps and I am sorry to have made this so difficult

 

[Fluff]

Given that, rows 1, 3 and 8 should all be yellow

But why 20 Jan 23 is less than Today & from what you said should be Green.

Once a date is entered in column E, no further changes to shading should occur as the data has now been collected. The cell should then stay the indicated color depending on the rules

I'm not sure that's possible

 

[ehall70]

But why 20 Jan 23 is less than Today & from what you said should be Green.



I'm not sure that's possible

20Jan23 is less than today, today is 27Jan23 and should be yellow. I have confused you enough. I appreciate your help and I will figure out some other way to manage it. I am sure I will send in other requests in the future. Thanks for offering this useful service.

 

[Fluff]

Ok.