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.

How to automate RYG balls using two different column values.


I am attempting to automate the RYG balls using two different columns, the End Date and % Complete columns. The logic is as follows: 

1. If the end date is greater than today, the RYG ball = Green IF([End date]2 > TODAY(), "Green"

2. If the % complete is greater than .99, the RYG ball = Green IF([% Complete]2 > .99, "Green"

3. if the % complete is empty/blank, the RYG ball = Yellow IF([% Complete]2 = "Empty", "Yellow"

4. Anything else is red


I have tried the following formulas to no avail. Any suggestions? 

=IF(OR([End Date]400 > TODAY(), [% Complete]400 > 0.99, "Green")), IF([End Date]400 = Blank, "Yellow", "Red")

=IF(OR(End Date > TODAY(), [% Complete]2 = "Full"), "Green", IF(ISBLANK([End Date]2 , "Yellow", "Red"))


  • KrisWalsh
    KrisWalsh ✭✭✭✭✭
    edited 11/28/17

    Hi Jawanza,

    How about this?

    If your task is done or not due yet, then Green:

    IF(OR($[% Complete]2 = 1,$[End Date]2 > TODAY()), "Green",

    If your task is not done and the due date has past then Red, otherwise Yellow:

    IF(AND($[% Complete]2 < 1,$[End Date]2 <= TODAY()), "Red", "Yellow"))

    All together:

    =IF(OR($[% Complete]2 = 1,$[End Date]2 > TODAY()), "Green", 

    IF(AND($[% Complete]2 < 1,$[End Date]2 <= TODAY()), "Red", "Yellow"))

    I strongly suggest a text editor like Notepad++. Use Visual Basic (VBA) as the language.  

    Good Luck!  (:

    Text Editor.jpg

This discussion has been closed.