AUTOMATE RYGB

AUTOMATE RYGB

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

 

Tagged:

Comments

  • L@123[email protected] ✭✭✭✭✭

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

  • 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, [email protected])

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.