Setting RYGG Based on 2 Columns

Nick Johnson

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.

 

 

Nick Johnson

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?

J. Craig Williams

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

Nick Johnson

Thanks Craig, worked like a charm.