3

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?

Functionality
Industry
Department

Comments

=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. 

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.