IF/AND formula for RYGG balls (for status and due date)

Options

Hi, I've combed through the community site posts but have been unable to solve the issue with my formula. I need the parameters below with #4 taking priority over #3. They "Cancelled", "On hold", "Complete" and "In Progress" are working but adding in the "Due" does not work.

Any help is greatly, greatly appreciated!!!

The formula I am using is:

=IF([Status Description]@row = "Cancelled", "Gray", IF([Status Description]@row = "On hold", "Gray", IF([Status Description]@row = "Complete", "Green", IF([Status Description]@row = "In Progress", "Yellow", IF([Status Description]@row = "In Progress", AND(Due@row < TODAY(), "Red"))))))

  1. If "status description" is "On hold" or "Cancelled", ball should be gray
  2. If "status description is "Complete", ball should be green
  3. If "status description" is "In Progress", ball should be yellow
  4. If "status description" is "In Progress" AND "Due" is past TODAY, ball should be red

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Options

    Hi @msamo88,

    Try moving the AND() around.

    =IF([Status Description]@row = "Cancelled", "Gray", IF([Status Description]@row = "On hold", "Gray", IF([Status Description]@row = "Complete", "Green", IF([Status Description]@row = "In Progress", "Yellow", IF(AND([Status Description]@row = "In Progress", Due@row < TODAY()), "Red")))))

    Hope this helps,

    Dave

  • msamo88
    msamo88 ✭✭
    Options

    Hi Dave, thank you for responding so quickly! I tried your formula and it all works except the very last part (below). If I select "In Progress" and enter a date, under "Due" column, older than today the ball stays yellow. Alternatively if I enter a date older than today I do not get a red ball but if I select "In Progress" the ball that shows up is yellow. Any thoughts?

    IF(AND([Status Description]@row = "In Progress", Due@row < TODAY()), "Red")))))

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    Hi @msamo88

    The formula stops running when it reads a true statement, so stops at the first in progress true statement.

    You can fix that by swapping the two statements around

    so from this

    =IF([Status Description]@row = "Cancelled", "Gray", IF([Status Description]@row = "On hold", "Gray", IF([Status Description]@row = "Complete", "Green", IF([Status Description]@row = "In Progress", "Yellow", IF(AND([Status Description]@row = "In Progress", Due@row < TODAY()), "Red")))))

    To this

    =IF([Status Description]@row = "Cancelled", "Gray", IF([Status Description]@row = "On hold", "Gray", IF([Status Description]@row = "Complete", "Green", IF(AND([Status Description]@row = "In Progress", Due@row < TODAY()), "Red",IF([Status Description]@row = "In Progress", "Yellow","")))))

    Hope that helps

    Thanks

    Paul

  • msamo88
    msamo88 ✭✭
    Options

    Hi Paul,

    Thank you, that fixed it!!

    Also, thank you Dave!! I was at this for hours :)

  • msamo88
    msamo88 ✭✭
    Options

    Hi again,

    Have one additional question: I want to change the end of the formula (#1 below) to instead report what I am trying to do in #2 below, however, it is not working, receive the error #Unparsable. Any thoughts?

    #1: IF([Status Description]@row = "In Progress", "Yellow","")))))

    #2: IF(AND([Status Description]@row = "In Progress", Due@row < TODAY()), "Red", IF(AND([Status Description]@row = "In Progress", Due@row >TODAY ()) "Yellow","")))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @msamo88 That's exactly how your formula is currently working.

  • msamo88
    msamo88 ✭✭
    Options

    Hi Paul, I agree that is how the formula is currently working but when I enter the #2 formula I receive an error message of #Unparsable. I don't know if I am missing something in the formula.

    I would like the formula to return the parameters below. Any guidance is greatly appreciated :)

    1. If "status description" is "On hold" or "Cancelled", ball should be gray
    2. If "status description is "Complete", ball should be green
    3. If "status description" is "In Progress", AND "Due" is before TODAY, ball should be yellow
    4. If "status description" is "In Progress" AND "Due" is past TODAY, ball should be red


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Your 4 parameters are already covered. There should be no reason to adjust it to your #2 attempt. @Paul McGuinness's suggested formula already covers what you want it to cover. Trying to add in the extra variable is simply adding unnecessary complexity to a formula that already works how you want it to work.

  • msamo88
    msamo88 ✭✭
    Options

    Thank you Paul, I see what you are saying. The reason I was considering changing the formula was because I had a couple of scenarios where the "status description" was "In Progress" and the "Due" date was past TODAY and the the ball was red BUT folks changed the date to reflect a date beyond today and they ball remained red, when I think it should have changed to yellow because the date was now in the future from today. I hope this explanation makes sense.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I understand what you are saying, but the formula SHOULD have been working that way without modification. Are you able to provide a screenshot of a couple of these rows as well as a screenshot of the formula open in the sheet as if you are about to edit it?

  • msamo88
    msamo88 ✭✭
    Options

    Sure thing Paul, please see below. You'll notice that on line 3 the "Due" date is post today so ball is yellow, this is correct. If you change the date to pre today the ball is red and this is also correct. However, if the "Due" date is deleted the ball remains red, I instead need it to be yellow if the "Due" date is blank.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    In that case you would want to modify your existing formula like so:

    =IF([Due Date]@row = "", "Yellow", original_formula)

  • msamo88
    msamo88 ✭✭
    Options

    Thank you Paul, it now works perfectly!!! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!