Color coding a cell based on dates in multiple other cells

Question

When I submit a project, I have to track it based on;

Date Submitted (Column E)

Date Reviewed (E+90days in column F)

Date for Follow up (E+180 in column G)

Date Finalized (in column E).

Using Conditional Formatting, based on "todays date" I am trying to get cell G (Date for Follow up) to show no fill if it is under 90 days since submission, to show yellow if it is past 90 days, red if it is past 180 days and green if finalized.

Under the date of review, I have the formula =IF(ISBLANK(E2),"",E2+90) to calculate the date of review

Under the date for follow up I have =IF(ISBLANK(E2),"",E2+180) to calculate the date of follow up

Is this why I cannot get the conditional formatting to work right. I have tried a variety of formulas but cannot figure it out.

Date Submitted	Date for Review	Date for Follow up	Date finalized
6/18/2024	9/16/2024	12/15/2024

Answer 1

Select H2:H6 or however far down you want. H2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND($E2<>"", $E2<TODAY()-90)

Click Format...
Activate the Fill tab.
Select yellow as fill color.
Click OK, then click OK again.

Repeat these steps, but with the formula

=AND($E2<>"", $E2<TODAY()-180)

and red as fill color.

Finally, repeat them with

=$H2<>""

and green as fill color.