How do I use conditional formatting - Highlight a cell if the completition date is 7 days past due?

Keara Cecil

Thanks in advance.

Sameer Karkhanis

May I ask you what happens in case the completion date is more than 7 days past due? Is it not important to track those?

The reason I am asking is because, if you use the conditional criteria "is in the last (days)" with days=7, then for all the records with completion date more than 7 days the conditional formatting will not be applied. Aren't those still important to be formatted and tracked?

Why not just use "is in the past" condition so that anytime overdue will be formatted?

Bassam Khalil

Hi @Keara Cecil

Hope you are fine, please try the following:

create a helper column call it "Check" and use the following formula for it ( convert it to column format formula)

Check =IFERROR([completion date]@row - [Due Date]@row, "")

then use the following conditional formatting:

this is the result:

CAS the CSA

Say I don't want three columns for every date I'm tracking, (date completed)(duration valid)(exp. date), Say I just want to put in the completion date and write a conditional format to turn it a color when it is more than x days in the past.

I want to record completion dates. I don't want to record exp. dates.

I just want it to turn yellow when it is however many days old, I plug into the conditional format, but not before then.

It should be as simple as today()>= (completed date)+90 but that does not seem to be the case.