RYGG Automated

isalinosousa
edited 12/09/19 in Smartsheet Basics

Hi all,

I am unsuccessfully trying to create a formula for the following RYGG column:

Column [Project at Risk] with the RYGG:

  • If [PROJECT START] (date) is more then a day over I want the [PROJECT@RISK] to display "RED" ball
  • If [PROJECT START] (date) is less then 7 days away I want the [PROJECT@RISK] to display "YELLOW" ball
  • If [PROJECT START] (date) is more then 7 days away I want the [PROJECT@RISK] to display "GREEN" ball
  • If another column [ACTUAL PROJECT START] has any DATE in it (not blank) I want the [project at risk] to display a "GREY" ball

I would be extremely grateful for any information you can provide to help.

Thanks!

 

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Can you maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, [email protected])

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • eric.o
    eric.o Employee

    Hello,

     

    When creating nested IF Functions it is best to break out the formula to achieve each individual pieces singularly then put them together into one formula, this ensures each piece works as desired. For the above-stated desires they may look like this.

     

    1. =IF([PROJECT START]@row<TODAY(), "Red")

    2. =IF([PROJECT START]@row<TODAY(+7), "Yellow")

    3. =IF([PROJECT START]@row>=TODAY(+7), "Green")

    4. =IF(NOT(ISBLANK([ACTUAL PROJECT START]48)), "Grey")

     

    As a note when utilizing formulas order of operations affects the results of the formula. For example, if Yellow came before red you would never see the ball turn red because technically any negative number is less than today + 7. The combined formula could look like this to achieve your desired goal.

     

    =IF(NOT(ISBLANK([ACTUAL PROJECT START]48)), "Grey", IF([PROJECT START]@row<TODAY(), "Red", IF([PROJECT START]@row<TODAY(+7), "Yellow", IF([PROJECT START]@row>=TODAY(+7), "Green"))))

     

    Cheers, 

    Eric  

    Smartsheet Support