# RYGG Automated

Options
edited 12/09/19

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:

• ✭✭✭✭✭✭
Options

Hi,

Can you maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• Employee
Options

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&lt;TODAY(), "Red")

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

3. =IF([PROJECT START]@row&gt;=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&lt;TODAY(), "Red", IF([PROJECT START]@row&lt;TODAY(+7), "Yellow", IF([PROJECT START]@row&gt;=TODAY(+7), "Green"))))

Cheers,

Eric

Smartsheet Support