Conditional Formatting Clear if another cell is not empty

apologies if that has already been answered – I’ve tried searching but nothing that is specific for this.

I am fairly novice with sheets and cant share the whole thing due to potentially confidential nature of the information.

The purpose of my sheet is to monitor dates for projects using colours.
enter image description here

I don’t want to focus too much on the date completed rows, more the rows with the projects on. It has a script in there to count the coloured cells but not sure if this interacts with my requests but thought I would include this in case.

So far, I’ve used to standard conditional formatting that doesn’t require myself to put a formula. So when the date on the (project name) row is today or yesterday (meaning it’s late) it goes red. When the date is tomorrow, it goes orange as warning.
A couple of things that I want to do are

  • I want it to be orange for longer than just the one day but I am not sure what formula to use. I’ve tried simple stuff like conditional formatting, date is tomorrow +4, as that makes sense to me but it doesn’t work, as, quite novice.
  • The second thing I want to happen is, I want to the red to stay longer to, more than just today yesterday. Again, I’ve tried to basic conditional formatting, date is today-5, as again, it just makes sense to me.
  • The final thing I want to happen is, when a date is inputted into the date completed field, I want the colour in the project name row to go clear, as if to say, something has been handed in, it’s no longer upcoming or late. For example, Take the payment holiday approaching exit. It is red, as it was yesterday. When I put the date that it was completed in for this, I want it to go clear because it’s been completed.
    I don’t know if it wont do this as I have 3 conditional formatting over it already.

I hope this is clear enough, I tried to be concise and give context. If anyone can help, it would be much appreciated.

Go to Source
Author: Daniel