Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Automating RYG balls

Greg Hawk
Greg Hawk
edited 12/09/19 in Archived 2015 Posts

I would like to automate the RYG balls in my sheet to turn “Green” if the Due Date is in the future, “Yellow” if the Due Date is today, and “Red” if the Due Date is in the past. Is this something Smartsheet can do?




  • Travis
    Travis Employee
    edited 02/20/15

    Good question, Greg! Using formulas, you can automate RYG balls to change colors based on multiple aspects of your sheet, including the Due Date.


    Here’s the formula that would be added to a RYG cell that will show “Green” if the Due Date is in the future, “Yellow” if the Due Date is today, and “Red” if the Due Date is in the past:


    =IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Green")))


    In this formula, Due Date is the column name and the 1 is row that contains the formula. This formula’s cell references would need to be updated to match the data (column name / row number) in your sheet.


    Here’s information from our help center on formulas: http://help.smartsheet.com/customer/portal/articles/775363-using-formulas


    And here’s a blog post on automating RYG balls: http://www.smartsheet.com/blog/support-tip-automate-RYG-balls

  • Thanks Travis! This is perfect!

  • Jeremy Michels
    Jeremy Michels ✭✭✭✭✭
    edited 04/06/15

    I have used this so much.

  • What about working between a start date and end date? Seems like I need an "and" statement?


  • Travis
    Travis Employee

    Todd, try this to get a result if *today* is between a start date and end date:


    =IF(AND(TODAY() < [End Date]5, TODAY() > [Start Date]5), "Green")

  • Thanks Travis! Works like a charm!


    What about green if = or > then start date? Adding >= didn't make it recongnize today's date should be green.


    I also want it to be red if before start date or after end date? Any thoughts?


    I messed with it, but couldn't get it.....

  • Travis
    Travis Employee
    edited 09/18/15

    Here you go Todd:


    =IF(AND(TODAY() <= [End Date]5, TODAY() >= [Start Date]5), "Green", "Red")


    All we need to do is add = to the formula in the two spots shown above and add an "if this is not true" parameter (red).


    This will show green if *today* is = or > the start date and < or = the end date. It will show red if *today* is < start date or  > end date.

  • Thanks Travis! Very cool... :-)

  • Scott Pederson
    edited 10/08/15

    Could an "and" statement go further and include a reference to an additional column? I would like to use ryg+gray balls with gray to = a past date and include an "and", "if" reference to a checkbox column looking to see if it has been selected.  This would provide me the additional visual aid of past due and completed. Red would then only = past due dates and not marked complete providing me a quick visual alert to tasks overdue and incomplete.  Could someone help me with that formula?   Pleeeease! ...Begging.


    Here's what I have so far,



    =IF(AND([Finish]3 < TODAY(),[Complete]3=FALSE, "Red", IF([Finish]3 = TODAY(), "Yellow", IF([Finish]3 > TODAY(), "Green",IF(AND([Finish]3<TODAY(),[Complete]3=TRUE,"Gray"))))


    ...not sure it's possible to reference a checkbox cell. Would a checkbox be a True/False statement like in the above formula?

  • Travis
    Travis Employee

    Scott - you were close! 


    Here you go:


    =IF(AND(Finish3 < TODAY(), Complete3 = 0), "Red", IF(Finish3 = TODAY(), "Yellow", IF(Finish3 > TODAY(), "Green", IF(AND(Finish3 < TODAY(), Complete3 = 1), "Gray"))))


    When using checkboxes, flags, etc, 1 = enabled and 2 = disabled.


    To fix your formula, I changed TRUE and FALSE to 1 and 0 and closed your AND conditions with parenthesis. 

  • Scott Pederson
    edited 10/09/15

    Thank you so much Travis!

  • Hello,


    I am using the formula


    =IF(Complete6 <> 0, "Gray", IF(TODAY() - [End Date]6 > 0, "Red", IF(TODAY() - [End Date]6 > -3, "Yellow", "Green")))


    for the automated balls which is working great, though when receiving automated weekly reports the status balls do not change automatically in line with the new dates i receive on the report.  I am forced to go into each individual job sheet for these to update.


    Is there a way to have these automatically update without having to go into smartsheet?



  • Travis
    Travis Employee
    edited 12/03/15

    Hi Mike, formulas will only update when the sheet is opened. Do you have a sheet you open regularly? If so you can take a cell in that sheet and cell link it to each sheet that contains formula. Then, all you would need to do is change that one cell in your sheet and each connected sheet will update and will run any formulas. 

  • Travıs

    How can we add an AND condıtıon to thıs formula


    =IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Green")))


    to show green ıf [Complete]1 = 1 AND [Due Date]1 > TODAY()


This discussion has been closed.