AUTOMATE RYGB

jbusch30
jbusch30 ✭✭✭
edited 12/09/19 in Formulas and Functions

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:

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    =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". 

  • jbusch30
    jbusch30 ✭✭✭

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

  • 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"))

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

    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.

  • Attached is the screenshot of what the sheet looks like

    Smartsheet Project List.jpg

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

    Sorry for the late reply.

    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!