RYG Ball Formlas

Lori Khoury
Lori Khoury ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I am having a hard time putting together the formula for my Health Column- RYGB Ball.   I need the formula to be the following.  Any chance some one could help put this formula together for me?  Thanks

  1. If Status is Not Started then Health is Blank
  2. If Status is In Process and is equal to or past the Due Date then Health is Yellow
  3. If Status is In Process and before due date then Green
  4. If Status is Complete or Closed then Health is Blue
Tags:

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =IF(Status@row = "Not Started", "", IF(AND(Status@row = "In Process", [Due Date]@row <= TODAY()), "Yellow", IF(AND(Status@row = "In Process", [Due Date]@row > TODAY()), "Green", IF(Status@row = "Complete", "Blue"))))

     

    Out of curiosity... Were you wanting to do anything with the "Red"?

    Untitled.png

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lori Khoury
    Lori Khoury ✭✭✭✭✭

    Thank you Paul for this formula.  This is great!!!!  You saved me so much time from trying to figure this out.   I need to be able to select Red manually for the milestone plan that I am working on.  I tested it out and see that I can override the formula where I need to.  

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The only downside to manually selecting "Red" is that it will erase the formula. It's as simple as Copy/Paste to get it back in there, but I'm an automation junkie. I like to be able to input as little as possible and let the sheet do the rest. Haha

     

    Glad I could help though.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lori Khoury
    Lori Khoury ✭✭✭✭✭

    Maybe your right and I should add an At Risk status so I can keep it automated.  I updated the formula to include Red and it worked.  Thanks again

    =IF(Status@row = "Not Started", "", IF(AND(Status@row = "In Process", [Due Date]@row <= TODAY()), "Yellow", IF(AND(Status@row = "In Process", [Due Date]@row > TODAY()), "Green", IF(Status@row = "Complete", "Blue", IF(Status@row = "At Risk", "Red")))))

     

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!