Conditional Formatting Multiple Rules

**ems.payroll** · 10-02-2008, 10:14 AM

I am using conditional formatting to highlight a cell orange after one week, and then red after two weeks. This is the rule I am using for one week, which works:

=AND(X2="",U2<>"",V2="",W2="",F2<=F2+7)

This is what I did for two weeks, but I can't get it to work, the cell remain orange:

=AND(X2="",U2<>"",V2="",W2="",F2<=F2+14)

Any suggestions on how to fix this?

**rwgrietveld** · 10-02-2008, 10:24 AM

F2<=F2+7 is always true as 0<=7, this does not make sense.
use F2<=Static Date+7 instead

**ems.payroll** · 10-02-2008, 10:39 AM

Here's the situation. I need to flag each person who does not write an order in their first week, and again if they don't write an order in two weeks. So they dates are different for everyone.

**shg** · 10-02-2008, 11:36 AM

I didn't look closely at your formulas, but assuming they work, you need to reverse the order. The first condition that's satisfied dictates the format.

**ems.payroll** · 10-02-2008, 03:43 PM

When I do that, they come up red even if it's not past 14 days, and I'm not sure why? Any suggestions?

**shg** · 10-02-2008, 04:06 PM

Post a workbook?

**rwgrietveld** · 10-03-2008, 06:06 AM

Take this workbook as a starting point. It includes the CF:

Please Login or Register  to view this content.

Explination: MAX(--(C3=$C$2:C2)*(A$2:$A2)) is the previous date

**ems.payroll** · 10-03-2008, 09:29 AM

Sample.xlsx

Here's a sample.

**ems.payroll** · 10-07-2008, 12:31 PM

I figured it out!

To flag it orange after 1 week I did this:
=AND(AG3="",AB3<>"",AC3="",AD3="",F3+7<=TODAY())
And then to flag it red after 2 weeks, I did this:
=AND(AG3="",AB3<>"",AC3="",AD3="",F3+14<=TODAY())

Please mark this as solved.