RYGG Automated

isalinosousa

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!

 

Andrée Starå

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

eric.o

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