Automating RYG balls

Greg Hawk

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

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

Greg Hawk

Thanks Travis! This is perfect!

SashaR

Good Post!

Jeremy Michels

I have used this so much.

Todd Miller

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

 

Travis

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

Todd Miller

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

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.

Todd Miller

Thanks Travis! Very cool... :-)

Scott Pederson

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

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

Thank you so much Travis!

Mike Broecker

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?

 

Thanks!

Travis

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. 

Tariq Shaikh

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