Conditional Formatting color if today minus cell date is more than 90 days

Question

I want cells in Column M to get a color if date (in that cell) has reached 90 days and if Column A has the value "Onvoltooid"

I selected the column range and clicked on the item: Conditional Formatting. Here I set:

Format Cells if...
Date is
exact date..

then I wrote: TODAY() - DATE() > x //x = 90 days for test x = 1 day

and I selected a color.

But it does not work. is TODAY() - DATE() > x even correct notation?

Answer 1

The solution is a multi-criteria formula.

In my case you want both criteria to be true.

Use this: =and(criteria_1, criteria_2) in the Conditional Formatting functionality. Use this as the 'custom formula' entry.

=AND(TODAY() - M3 > 7, A3 = "Onvoltooid") 

this formula says:

Criteria_1: if date of TODAY minus Date-value in column M (from row 3) is higher than '7' days.

AND

Criteria_2: The value of that same row on another column (column A, row 3) is "onvoltooid" (which is a specific value)

Then give specific color to the selected range.

Answer 2

Be aware that constantly-changing terms like Today() take a lot of computing power. For what I did to make my spreadsheet work with multiple time cutoffs (within 90 days of TODAY, within 30 days of TODAY, before TODAY) was to create some helper cells where

A1: =TODAY

A2: =A1+30

A3: =A1+90

Then I was able to use the conditional formatting to just compare the cells. It was a lot simpler for me than trying to figure out how to get the equation to work inside the conditional formatting.

"If cell B5 < A3" (color), "If cell B5 < A2" (color), and "If cell B5 < A1" (color).

If it helps, I did manage to succeed on using an "IF" equation for Conditional Formatting before, using "False" as the Value_If_False line. The formatting ran, and was the colors I wanted.