Condtional formatting based on dates

The attached file has a planned (PLN) and actual (ACT) dates.

For the planned dates (PLN), I have a conditional formatting formula that highlights the cell when the date is within 20 days of expiring / passing. However, once the date expires (i.e. PLN date = 2/14/13 and TODAY date is 2/15/13), the cell will no longer highlight the cell.

I need a formula that will highlight the cell even after the date expires AND the actual date has not been updated (adjacent column of each PLN | ACT date(s)). Once the actual date is updated in the corresponding cell, than no longer highlight. I've tried conditional formulas but can't find one. Or is this a VBA task?

how do you tell if the date has been changed/updated?

In other words, is it because the actual is 'empty' that you know the planned hasn't been updated, or some other criteria?

yes...the question is not about whether or not the date is exceeded, it's about HOW to tell whether the date has been updated...if there is no reference to to tell if the date has been changed, how can you expect excel to either highlight/not highlight the cell?

Edit-
YOU might know the date has changed, YOUR managers might know the date has changed, but HOW is excel supposed to know?

THAT is what I've been asking all along...the ACT column is the switch, thank you...now we can proceed...

see if this is what you had in mind : rz6657.sol1.xlsx

Note- I applied the formula from b3-aa26 and used your first condition as well, if you don't want the 20 days or less as part of the rule change the rule to this:

Formula:

Please Login or Register  to view this content.

or this if the update rule is just that Actual contains a value>0

Formula:

Please Login or Register  to view this content.

Hope this helps

You are welcome!
Glad I could come up with something you could use