Function for RYGB Ball

Question

I'm trying to create a formula for the ball to automatically update based on a "Status" column and a "Modified Date" column. Here's what I have so far...part of it appears to be working, but I'm having difficulty with the Red and Yellow/Green part of the function. Can someone please help?

=IF(Status@row = "Executed", "Blue", IF(Status@row = "Canceled", "Blue", IF(AND(Status@row = "Waiting for Requestor Response", (([Modified Date]@row) < TODAY(-60))), "Red", IF(AND(Status@row = "WD Legal Review", (([Modified Date]@row) < TODAY(-60))), "Red", IF(AND(Status@row = "Customer Legal Review", (([Modified Date]@row) < TODAY(-60))), "Red", IF(AND(Status@row = "Sent for Signatures", (([Modified Date]@row) < TODAY(-60))), "Red", IF(AND(Status@row = "Waiting for Requestor Response", (([Modified Date]@row) < TODAY(-30))), "Yellow", "Green", IF(AND(Status@row = "WD Legal Review", (([Modified Date]@row) < TODAY(-30))), "Yellow", "Green", IF(AND(Status@row = "Customer Legal Review", (([Modified Date]@row) < TODAY(-30))), "Yellow", "Green", IF(AND(Status@row = "Sent for Signatures", (([Modified Date]@row) < TODAY(-30))), "Yellow", "Green"

Thank you!

Genevieve P. · Accepted Answer

Hi Shannon,

It seems like you may just have a few extra parentheses in there, which is causing sections to close off before you're ready to tell the formula to stop.

We can simplify this formula by using the OR function. I'll break it out by colour, first, then provide the full thing:

BLUE:

=IF(OR(Status@row = "Executed", Status@row = "Canceled"), "Blue",

RED:

IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-60)), "Red",

YELLOW:

IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-30)), "Yellow", "Green")))

FULL FORMULA:

=IF(OR(Status@row = "Executed", Status@row = "Canceled"), "Blue", IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-60)), "Red", IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-30)), "Yellow", "Green")))

ALTERNATE OPTION:

Can I also ask, are these the only types of statuses that you'll have?

Executed / Canceled / Waiting for Requestor Response / WD Legal Review / Customer Legal Review /Sent for Signatures

If so, we can simplify this even further, because you state at the beginning what to do for "Executed" and "Canceled"... therefore the formula will only continue to the next colour if the status is not one of those two. This means you don't have to list them at all and can just focus on the dates!

Try this:

=IF(OR(Status@row = "Executed", Status@row = "Canceled"), "Blue", IF([Modified Date]@row < TODAY(-60), "Red", IF([Modified Date]@row < TODAY(-30), "Yellow", "Green")))

Let me know if you have any questions about this!

Cheers,

Genevieve

Shannon Heward · Accepted Answer

Thank you so much!! This is perfect and I greatly appreciate your help! There are no other Status types...I was making it too complicated!