# RYG Automation

✭✭✭

Would like to automate the RYGB based on % Complete

Blue = 100% complete

Green = <100%

Yellow = 0% past Start Date OR <100% past Finish Date

Red = <100% 5 days past Finish Date

Already have a symbols column "RYG"

Already have "% Complete" column

## Best Answer

• ✭✭✭✭✭✭
Answer ✓

Hi Thai! Try this, assuming your finish date column is called [Finish Date]:

=if([% complete]@row = 1,"Blue",if(AND([% complete]@row < 1, TODAY()>([finish date]@row + 5)),"Red",if([% complete]@row = 0,"Yellow",if(AND([% complete]@row < 1, TODAY()>([finish date]@row)),"Yellow"))))

To translate:

If % complete is 100%, show blue.

If % complete is less than 100% and the finish date is more than 5 days in the past, show red.

If % complete is equal to 0, show yellow.

If % complete is less than 100% and the finish date is in the past, show yellow.

Let me know if it works!

Best,

Heather

## Answers

• ✭✭✭✭✭✭
Answer ✓

Hi Thai! Try this, assuming your finish date column is called [Finish Date]:

=if([% complete]@row = 1,"Blue",if(AND([% complete]@row < 1, TODAY()>([finish date]@row + 5)),"Red",if([% complete]@row = 0,"Yellow",if(AND([% complete]@row < 1, TODAY()>([finish date]@row)),"Yellow"))))

To translate:

If % complete is 100%, show blue.

If % complete is less than 100% and the finish date is more than 5 days in the past, show red.

If % complete is equal to 0, show yellow.

If % complete is less than 100% and the finish date is in the past, show yellow.

Let me know if it works!

Best,

Heather

• ✭✭✭✭✭

Hi @Thai La

Let's go with this:

=IF([Finish Date]@row>TODAY(), IF( [% Complete]@row =1, "Blue", IF(AND([Start Date]@row<TODAY(), [% Complete]@row =0), "Yellow", "Green")), IF([Finish Date]@row<TODAY(+5), "Red", "Yellow"))

Hope it helped!

• ✭✭✭
edited 02/09/21

Hi @Heather Duff your formula works.

I've added Green status to your formula, everything works now. Thanks again!

• ✭✭✭

Hi @David Joyeuse your formula almost worked, it was marking 100% complete items Red instead of Blue for some reason though

• ✭✭✭✭✭✭

Ah, perfect! Glad it worked.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!