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.

RYG Ball IF Statements

edited 12/09/19 in Archived 2016 Posts

I have been trying for a few a while now to get the IF statement to work for automating the RYG to work off of end date and % complete. I would like to have it work where if the it is 3 days from complete and below 50% complete have it as red. If it is 51% to 90% yellow and 90% to 100% green. If on the last day it is still in the 90% keep it green. If it passes the date and not complete then make it red. 


My IF statements are not working and I am confused now. 






  • Greg Gates
    Greg Gates ✭✭✭✭✭

    Hi Dan,


    This IF-statement gets a little complex since it involves a few different conditions. I think the following addresses all of your needs:


    =IF(AND(TODAY() - [Date Column]1 > 0, [% Complete]1 < 1), "Red", IF([Date Column]1 - TODAY() < 4, IF([% Complete]1 >= 0.9, "Green", IF([% Complete]1 > 0.5, "Yellow", "Red"))))


    I'm not sure what you want to have if the date is more than 3 days away, so I just leave it blank for now. Let me know if you don't understand how the formula works, or if it doesn't address all of your issues!



  • djm714

    Hi Greg,


    I was going to have it stay green until the it gets within 3 days. The IF statement that you did works great. 

This discussion has been closed.