Conditional Formatting based on dates (2)

**rz6657** · 03-05-2013, 12:46 PM

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.

**etaf** · 03-05-2013, 01:43 PM

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 could not see your conditional format in the cells

note sure you want to highlight planned dates > then today()
=and( actual date = "" , plandate >=today()-20 )

so to stop that
=and( actual date = "" , plandate >=today()-20, plandate <today() )

**dredwolf** · 03-05-2013, 03:03 PM

Highlight E5-Q5, CF->New Rule -> Use Formula :

Formula:

=OR(AND((E5-TODAY())<=20,(E5-TODAY())>=0),AND(TODAY()>E5,OR(E6=0,E6="")))

for all the rules

or:

Formula:

=AND(TODAY()>E5,OR(E6=0,E6=""))

if you wish to have the 20 days or less as a separate rule/color

Note these formulas are slightly different than the ones we used for your last question

See attached (Using the first formula)

Hope this helps

**arlu1201** · 03-05-2013, 03:17 PM

rz6657,

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread. http://www.excelforum.com/excel-form...-on-dates.html

Thread Closed.