How to use end date and percentage complete to automate RYG status

Matt W

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

Shaine Greenwood

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