#### 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

Options
edited 12/09/19

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?

Tags:

• Employee
edited 02/20/15
Options

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

• Options

Thanks Travis! This is perfect!

• Options

Good Post!

• ✭✭✭✭✭
edited 04/06/15
Options

I have used this so much.

• Options

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

• Employee
Options

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

• Options

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

• Employee
edited 09/18/15
Options

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.

• Options

Thanks Travis! Very cool... :-)

• edited 10/08/15
Options

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?

• Employee
Options

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.

• edited 10/09/15
Options

Thank you so much Travis!

• Options

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!

• Employee
edited 12/03/15
Options

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.

• Options

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.