# AUTOMATE RYGB

Options
✭✭✭
edited 12/09/19

Hello All,

I am trying to automate the blue ball based on complete and the red, yellow and green based on date. I have come up with the following formula but I am getting an UNPARSE any help would be great.

=If(Status1 =Complete, "Blue", IF(OR(ISBLANK([End Date]1), [End Date]1 >= Today(-5)), "Red", IF([End Date]1>=Today(-7), "Yellow", IF([End Date]1 <=Today(-10), "Green"))))

Tags:

• ✭✭✭✭✭✭
Options

=If(Status1 ="Complete", "Blue", IF(OR(ISBLANK([End Date]1), [End Date]1 >= Today(-5)), "Red", IF([End Date]1>=Today(-7), "Yellow", IF([End Date]1 <=Today(-10), "Green"

You were missing quotes around "Complete".

• ✭✭✭
Options

Thank you so much, its crazy how after you look at these for so long you miss the details

• Options

Hello All,

I am trying to set up some KPI's for engineering and I want to automate the RYGB balls to have them green if due date is greater than 10 days, yellow if between 10 and 5 days, and red if less than 5 days, I was able to get that to work but I have many projects that are low priority that the status is not started. So I would like to add to the formula something to change the color to blue if the status is not started, completed or cancelled. I cant seem to get that part to work or figure out what I am missing, any help would be appreciated.

=IF(([DUE DATE]2 - TODAY()) >= 10, "Green", IF(AND(([DUE DATE]2 - TODAY()) < 10, ([DUE DATE]2 - TODAY()) > 5)), "Yellow", IF(AND(([DUE DATE]2 - TODAY()) <= 5, (PRIORITY2 = "HIGH")), "Red"), IF(AND((STATUS2 = "COMPLETE"), (STATUS2 = "CANCELLED")), "BLUE"))

• ✭✭✭✭✭✭
Options

Hi,

Can you maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

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.

• Options

Attached is the screenshot of what the sheet looks like

• ✭✭✭✭✭✭
Options

Did you get it working or do you still need help?

Have a fantastic weekend!

Best,

Andrée

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!