Automating RGYB Balls With Date Ranges

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.


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.)

Status Formula.JPG


  • Mike Wilday
    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"))))

  • 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. 

    Status Formula 4-17 1.24PM.JPG

  • 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
    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"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!