Conditional formatting in Google sheet for finished/due tasks

user_1639556 0
Viewed 61

I have built a simple task tracker in Google sheet.

There is column B handling the status (In progress, Done) and Due date column C.
I have created to formulas: exact match for B that sets the color to green and date before today for C that sets the red color.

The problem is that they work independently. Red color is displayed for C even when B is Done.
And each rule highlights different column.

enter image description here

Text is exactly Done

enter image description here

Date is before =today()

enter image description here

How to make the date formatter ignore the past dues when the task status is set to Done?

conditional-formatting google-sheets-formula google-sheets
1 Answers

You mentioned

exact match for B that sets the color to green and
date before today for C that sets the red color

Try the following formula under Custom formula is

=AND(C3<TODAY(),B3<>"Done", C3<>"")

enter image description here

(Do adjust ranges to your needs)

user_15277800
Related