RYG Ball Automation

kwheeler

Hi there,

I am trying to automate the 'Health' column on a sheet. I have it set up to turn RYG when a certain status is selected but I would also like it to turn Red if the Due Date or Due for Review is a past date and the status is still listed as anything but 'Complete."

I currently have this formula in place

=IF(Status13 = "Not Started", "Red", IF(Status13 = "In Progress", "Yellow", IF(Status13 = "Complete", "Green", IF(Status13 = "Needs Edits", "Yellow", IF(Status13 = "Waiting For Review", "Yellow")))))

How would I add to it to make the date being in the past trigger a Red ball in the health column with the rest of the formula as well?

Mike Wilday

=IF(OR(Status13 = "Not Started", [Due Date Column Title]13 < today()), "Red", IF(Status13 = "In Progress", "Yellow", IF(Status13 = "Complete", "Green", IF(Status13 = "Needs Edits", "Yellow", IF(Status13 = "Waiting For Review", "Yellow")))))

Try this formula on row 13. 

Change the Due Date Column Title to whatever the actual due-date column title is. 

Paul Newcome

See if this covers what you need...

 

=IF(OR(AND(OR([Final Due Date]@row < TODAY(), [Due for Review]@row < TODAY()), Status@row <> "Complete"), Status@row = "Not Started"), "Red", IF(Status@row = "Complete", "Green", "Yellow"))

 

The first statement covers both of your red requirements of

1.a. Due Date OR Due for Review is in the past

and

1.b. Status is NOT Complete

or

2. Status is Not Started

The second IF covers the only reason for being Green (Complete)

After that you are showing everything else as Yellow, so instead of creating a bunch of IF's for each of those, you can just use "Yellow" in the third portion of the last IF statement (where you would put another IF statement), but instead we just tell it what to say if the first two IF's are false.

Paul Newcome

Looks like we were typing at the same time. Lol