Trying to get RYGB to work with two columns

Question

If the PO Process Status is "8 - PO issued" use Blue Harvey Ball.

Formula that works: =IF([PO Process Status]@row = "8 - PO issued", "Blue")

If the Pending as of date is less than x etc. use Harvey Ball...

Formula that works: =IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green"))

How do we combine? We have tried IF(AND, IF(AND(OR, IF(OR - nothing is working.

Amber Eakin · Accepted Answer

If I'm understanding this correctly, if the PO is issued, you want it to be blue. Otherwise, if it's pending, you want another color based on a date. Is that right? If so, give this a try:

=IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green")))

Let me know if that helps!

Amber Eakin · Accepted Answer

Hi @SandyK_GE - I would use the ISBLANK function instead. It would look like this. Let me know if this works for you!

=IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(ISBLANK([Pending As Of]@row), "", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green"))))

Amber Eakin · Accepted Answer

Oh! Delete all the closing parentheses at the end and try again. Sometimes if you have too few or too many, it does that. If there are none, it will auto-create the right amount.

SandyK_GE · Accepted Answer

Thank you. Works!  Can't thank you enough.

This formula works: =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green")))

This formula works: =IF([PO Process Status]@row = "8 - PO issued", "Blue", IF(ISBLANK([Pending As Of]@row), "", IF(TODAY(-4) > [Pending As Of]@row, "Red", IF(TODAY(-2) > [Pending As Of]@row, "Yellow", "Green"))))

If above formula fails the first time, remove all right parens at the end of the formula. Once you press enter, it will work.