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

msamo88

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

DKazatsky2

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

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

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

Hi Paul,

Thank you, that fixed it!!

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

msamo88

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

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

msamo88

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

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

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

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

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

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

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

msamo88

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

Paul Newcome

Happy to help. 👍️