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 use end date and percentage complete to automate RYG status

edited 12/09/19 in Archived 2016 Posts
12/17/16 Edited 12/09/19

Hi everyone, 


Maybe my brain is struggling with IF and AND statements, but this is defeating me. 


I am trying to use the end date of a task compared to today's date and the percentage complete (great than or equal to 75%) to drive my status column of red, yellow, green balls. 


I think I need to find a way to combine the following forumlas:

Task is at or past the due date and the percentage complete is less than 100% = RED

=IF(TODAY() - [End Date]3 <= 0, "Red")

=IF([% Complete]# < 1 , “Red”)


Task is within 5 days of the due date and the percentage complete is 75% or greater = YELLOW


=IF(TODAY() - [End Date]# >= -5 , “Yellow”)

=IF([% Complete]# >= .75 , “Yellow”)


Task is at or past the due date and the percentage complete is 100% = GREEN

=IF(TODAY() - [End Date]# >= 0 , “Green”)


=IF([% Complete]# = 1 , “Green”)


Any suggestions would be great!






  • Hello Matt,


    You might try using this syntax instead:


    =IF(AND(TODAY(-5) >= Finish1, [% Complete]1 >= 0.75), "Yellow")


    You'll want to adapt this for your different symbols, and nest it in one formula:


    =IF(AND(...), IF(AND(...), IF(AND(...))))


    More on formulas can be found in our Help Center: https://help.smartsheet.com/articles/775363-using-formulas

  • Sarah McMorrowSarah McMorrow ✭✭✭✭✭

    I'm struggling with this as well.

    I tried using the formula Shaine suggested and I can get one to work, but as soon as I add additional IF(AND) statements I get an #UNPARSABLE error.


This discussion has been closed.