0

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?

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.