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

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    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

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    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

  • David Joyeuse
    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
    Thai La ✭✭✭
    edited 02/09/21

    Hi @Heather Duff your formula works.

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

  • Thai La
    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
    Heather Duff ✭✭✭✭✭✭

    Ah, perfect! Glad it worked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!