RYGG based on dates and complete check box with blanks

Heather Holderness

Hi there,

I have had success with most of this equation, the part that is stumping me is that I want to have the health status turn "Gray" if there is no "Finish" date to indicate that it has not yet been started/not yet being worked on. I have the other colors to be set to work respectively with the "Finish" date and the "Complete" yes/no and they seem to be working the way I want them but I cannot get the gray to work when there is no date/that cell is blank. Anyone willing to help? Thank you!

Current syntax:

=IF(AND(Complete4 = 0, Finish4 <= TODAY(-2)), "Red", IF(AND(Finish4 > TODAY(-10), Complete4 = 0), "Yellow", IF(AND(Finish4 > TODAY(-5), Complete4 = 1), "Green", IF(AND(Finish4 = " ", Complete4 = 0, "Gray")))))

L_123

You have 2 errors in this. First, the health is showing green, which means that they grey part of the if statement isn't reached, so we need to make a change to the green criteria, and second Finish4 = " " isn't going to catch on the blank because of the space. You want to use either isblank(Finish4) or Finish4 = "". Give the below a try.

=IF(AND(Complete4 = 0, Finish4 <= TODAY(-2)), "Red", IF(AND(Finish4 > TODAY(-10), Complete4 = 0), "Yellow", IF(AND(isdate(Finish4),Finish4 > TODAY(-5), Complete4 = 1), "Green", IF(AND(Finish4 = "", Complete4 = 0, "Gray")))))