Automating RGYB Balls With Date Ranges

Nicole Stewart

I want the status balls to automatically update based on the due date - but I also want the status to go blue after the "Item Completed" column is checked.

Ie.

If End Date is in the past, or due within the next week = Red

If End Date is Due within the two weeks = Yellow

If end Date is Due in three weeks or more = Green

If "Item Complete" is Checked = Blue

I currently have a working formula, but it is only based on past, today, and future - not on date ranges. (See image.)

Mike Wilday

Try this one:

It's written for row 23, so adjust the row number as needed. I changed the requirements of Green so that if its less than the criteria for yellow, then it will display green. Let me know if it works. 

=If([Item Complete]23 = 1, then "Blue", IF([End Date]23 >= Today(-7), "Red", IF([End Date]23 >=Today(-14), "Yellow", IF([End Date]23 <=Today(-14), "Green"))))

Nicole Stewart

Mike, Thanks for your help!  I'm getting an unparseable error.

I had changed a column name since my post yesterday, but I changed it back to match the formula. 

Nicole Stewart

Actually, Mike.  It works perfectly! There was a "then" that I had to remove from the formula.  However, I have discovered that I need one more command.  

 

IF End Date is blank, then red.  I tried using "ISBLANK", but got an unparseable error.

Mike Wilday

Whoops. I see that then in there... Try this one: U added an OR statement. 

=If([Item Complete]23 = 1, "Blue", IF(OR(ISBLANK([End Date]23), [End Date]23 >= Today(-7)), "Red", IF([End Date]23 >=Today(-14), "Yellow", IF([End Date]23 <=Today(-14), "Green"))))