RYG Automation

Thai La

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

Heather Duff

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

Heather Duff

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

David Joyeuse

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!

Thai La

Hi @Heather Duff your formula works.

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

Thai La

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

Heather Duff

Ah, perfect! Glad it worked.