Conditional Formatting for Dates in the Past

[johnification]

I am using Excel version 2007


I am trying to use conditional formatting to highlight cells with dates with different colors as they go further in the past. The following is what I am trying to accomplish:


If the date in the cell is between NOW and 180 days old = Green

If the date in the cell is between 180 days old & 240 days old = Orange

If the date in the cell is older than 240 days = Red


I have tried the formulas "cell value is less than NOW()-240", etc, and it just won't work for me. I've even tried putting them in different orders. If someone can help me I would be INCREDIBLY GRATEFUL and would almost entertain the idea of giving up my first-born for this answer.. almost. HELP!!

 

[Hui]

Johnification


Firstly, Welcome to the Chandoo.org Forums


You need to add 3 Conditional Formats to the range, 1 for each color


Assuming that your Range is A1:A10

select A1:A10


Clear the existing CF's


Then add the 3 CF's


=Now()-A1>240

Select a Format Red

Stop if True not Ticked


=Now()-A1<240

Select a Format Orange

Stop if True ticked


=Now()-A1<180

Select a Format Green

Stop if True ticked


Apply the above


In the CF manager the last CF entered should be listed at the top

and the first CF listed above should be at the bottom

 

[johnification]

Thanks for the welcome, Hui... and more importantly, THANKS for the answer!! It totally worked!


-John