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!

 

Thanks,

 

-matt

Comments

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

    HELP!!

This discussion has been closed.