0

I have a list of tasks, and I've use conditional formatting to highlight due dates that fall within the next 7 days so this text appears in red. In another column I have the status of each task (In Progress, Complete, Not Yet Started or Awaiting Response)

The problem I'm now having is all the tasks I have completed are still showing as red in the due date column, as the dates pass. What I'd like to do is clear the formatting, only if the text says 'Complete' in the status column. I don't want to remove completed tasks from the spreadsheet.

How would this be possible?

enter image description here

Improve this question
2
  • You can modify your existing formattings to highight with red only if status is not Completed. Or add a new condition to stop checking others in case status is complete... . It's difficult to give full answer without knowing how your existing formattings look like.
    – Máté Juhász
    Jun 30, 2017 at 9:33
  • Thanks! I was a bit lazy with the formatting on the due dates and just used this: Dates in yellow: Format only cells that contain Dates Occurring This Week. Dates in red: Format only cells that contain Cell Value Less than or equal to =TODAY().
    – Roxy
    Jun 30, 2017 at 10:04

1 Answer 1

Reset to default
0

You just need to use the formula option in conditional formatting with some basic logic like =AND($C2<TODAY()+7,$E2<>"Complete"). Not the absolute references to the columns (the "$s"); these are critical.

See the screen grab here: conditional formatting with formula using AND function

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .