Change cell color according to date

**hmvl44** · 08-08-2013, 09:58 AM

I read the following instructions in this thread:
http://www.excelforum.com/excel-form...-new-post.html

but am running into a problem where cells without a value (entered date) are turning to red or yellow etc. If there is no value, I'd like the cells to remain white. Can you help me with a formula which will do this?

I want cells to turn to red if the date is greater than or equal to 14 days prior to today's date.

I am currently using Excel 2007 and Conditional Formatting with the following rule:

=$D2:I500<TODAY()-14

I'm new to the programming language so appreciate any explanation and advice. Thank you.

**Pepe Le Mokko** · 08-08-2013, 10:13 AM

Select your range ( say $D$2:$i$500) and enter following CF =and(D2<>"",D2>=today()-14)
Format red as needed

**rajeshturaha** · 08-08-2013, 10:14 AM

I think this will solve your problem

Formula:

Please Login or Register  to view this content.

in CF in D2

**hmvl44** · 08-08-2013, 12:58 PM

Thank you both. I've tried both formulas and neither is working. I'm still getting red in cells where there is not a value and I'm also getting them where the date is further out than today's date.
Any other suggestions for me to try?