conditional formatting cell colours based on deadlines and info in other cells

Question

Hi! I'm having a problem with conditional formatting. I have a work tracker that has a list of deadlines in cell I (starting I3, I'd be adding dates the more I get tasks), which I would like to highlight when the day comes closer:

• green if the DL in column I is more than 6 workdays away
• yellow if the DL is between 6-3 workdays away, and
• red if the DL is less than 3 workdays away.

Additionally, in column J I'll be marking down when I have completed the DL, and hence would like the same row cell in column I to turn back to white if I've added a date on the J column so that it's easier to keep track. (so if the DL cell is red because the DL is still 2 days away, I'd like it to go white if I add the completion date in cell J. Same for all colours)

I've managed to create the colour codes, but when I try to add the condition of J column, it seems to mess with the whole system. Any tips?

Answer 1

Good night

I hope the rules below help.

Go to conditional formatting and enter New Rule, option: Use a formula to determine which cells should be formatted.

Green

=AND((today()-I3)>6;J3<>"completed")

Yellow

=AND((TODAY()-I3)<=6;(TODAY()-I3)>=3;J3<>"completed")

Red

=AND((TODAY()-I3)<3;J3<>"completed")

I look forward to hearing back if you helped, hugs!