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

Rosa-Lotta Järvinen 0 Reputation points

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?

A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,095 questions
{count} votes