Setting RYGG Based on 2 Columns

I'm trying to set up RYGG based on a Finish Date column and a Status column. Here is what I have so far:

=IF(Finish6 < TODAY(), "Red", IF(Finish6 = TODAY(), "Yellow", IF(Finish6 > TODAY(), "Gray")))

What I would like to accomplish past this:

Yellow if 5 days prior to Finish

Geen if 6 or more days prior to finish

Gray if Status column shows Complete (Complete in the Status column is set by another formula based on 100% in a % Complete column).

The above formula is working as should, I am having trouble adding the prior dates and the value from the Status column.

Find more posts tagged with

Formulas

To add to my above statement, would an AND be necessary so when the FINISH date has passed but it is complete it would stay GRAY and not turn RED?

NIck,

Try this formula:

=IF(Status23 = "Complete", "Gray", IF(Finish23 < TODAY(), "Red", IF(Finish23 <= TODAY() + 5, "Yellow", "Green")))

for row 23

You don't need an AND because the RYGG is gray, regardless of the date if completed, so that comes first in the formula to make it easier.

Hope this helps.

Craig