RYG Formula

Deb Fuller

Hello,

I am trying to determine how to get the RYG formula to calculate correctly for my child rows. The following is my formula and I have attached an image of my current sheet. (Thank you in advance).

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

RYG Forumula.png

Brian Wilson DC

@Deb Fuller Deb, you will need to use a combination of IF(AND statements. See below for the formula I am using on the table I have also pictured below:

=IF(Status@row = "Complete", "Green", IF(AND(Status@row = "In Progress", [Finish Date]@row > TODAY()), "Green", IF(AND(Status@row = "In Progress", [Finish Date]@row = TODAY()), "Yellow", IF(AND(Status@row = "In Progress", [Finish Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [Finish Date]@row > TODAY()), "Green", IF(AND(Status@row = "Not Started", [Finish Date]@row = TODAY()), "Yellow", IF(AND(Status@row = "Not Started", [Finish Date]@row < TODAY()), "Red", "")))))))

I hope this helps!

-Brian

Deb Fuller

Hi Brian,

This is helpful, however, when I change the In Progress row to today's date it remains green yet turns the parent row yellow. Is there something else that needs to be added to the formula to ensure that specific row turns yellow?

Thanks again.

Deb