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

Options
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

• Employee
Options

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

• Options

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.