Help with formula with multiple conditions to auto-update RYG ball

Jen Garijanian

I am trying to create a formula with multiple conditions in order to automatically change the color of the RYG ball.

IF the Status = Not Started/In Progress/Blank AND the End Date is less than TODAY () then the ball should be Red

OR IF the Status = Not Started/In Progress/Blank AND the End Date is in the next 7 days then the ball should be Yellow

otherwise the ball should be Green

I currently have this formula =IFERROR(IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(+7), "Yellow", "Green")), " ") but I just need to add other conditions for Red & Yellow status.

Thanks in advance for any help!

Paul Newcome

Try something like this...

=IFERROR(IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow", "Green")), ""), "")

Paul Newcome

Try something like this...

=IFERROR(IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow", "Green")), ""), "")

Jen Garijanian

That works - thank you Paul!

Paul Newcome

Happy to help. 👍️

Jen Garijanian

@Paul Newcome this formula works great but when my Status field is blank (no End Date entered) I want the default of my RYG to be blank. Is there a way to add that into the existing formula you helped me with?

Paul Newcome

Yes. Your initial post had a "blank status" included as one of the possible options for generating a color. If you want it to be left blank, then we should be able to just remove that particular bit from the original formula like so:

=IFERROR(IF(OR(Status@row = "Not Started", Status@row = "In Progress"), IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow", "Green")), ""), "")