Single cell conditional formatting, different formatting at different times

[namzed]

I have a spreadsheet to track projects with deadlines. I had one level of conditional formatting already setup, and it worked fine. I would like to add another condition.

Most of the time I have a default deadline of 14 days after I receive the project, and that is called the projected publish date. If I am given a hard deadline by the client and that deadline occurs before our projected publish date, the cell conditionally fills with red. That works perfectly.
I have decided that if that project is completed/published before the client deadline, the red cell should go back to normal formatting with no fill color. I don't know how to achieve this second condition. Please see attached file for an example spreadsheet with my current setup.


Thanks for your help.

[protonLeah]

Please Login or Register  to view this content.

[namzed]

I'm sorry I'm swamped with other stuff and can't completely digest that formula. Would I use that as the secondary condition, or use that as the only condition?

[protonLeah]

It replaces the current CF formula
Exp:
If proj publish date is greater than the deadline date and the publish date is empty, then RED
IF proj publish date is greater than the deadline date and the publish date is greater than deadline date, then RED

[namzed]

Your equation worked. Thanks.