Conditional Formating for dates beyond 30, 60, 90 to be high light

[dmorlando]

My spread sheet has sales prospects names, numbers, titles, etc. I also have
a column titled "last date of contact" with dates in the cells. I want the
dates in these cells to change color or the cell to be high lighted when that
date goes beyond 30, 60 or 90 days.
Thanks

 

[Mike H]

Hi,

Select the range of dates then

Format|Conditional format

cell value is - less than - =TODAY()-90
format - pick a colour

ADD

cell value is - less than - =TODAY()-60
format - pick a colour

ADD

cell value is - less than - =TODAY()-30
format - pick a colour

OK

Mike

 

[dmorlando]

Mike, thank you for the info. I need to test my understanding. based on the
conditional formating you gave me what should happen is; once one or more of
the dates in this column go beyond 30, 60, 90, days from todays date or what
ever the date is each time I open the work book then I should see the colors
I picked.....correct? The only date that changed in color is one from 2008
which in fact wold be 90 days old from today and red is the color I chose for
90 days.

I'm still a little confused.

 

[Mike H]

Hi,

it's important you enter the CF's in the order I gave you because if you
enter =today()-30 first then it will never evaluate today()-60 etc. Once
condition 1 evaluate as TRUE it stops and doesn't even bother with the next 2
conditions.

So if a client was visited on 27/2/2009 and the worksheet calculates then
none of the conditions evaluate as true but as today() moves on the visited
date becomes more remote from today and and eventually gets to today()-30 =
TRUE then conditions 1 & 2 evaluate as FALSE but condition 3 evaluates as
TRUE and the cell changes colour.

As we move on in time then ultimately today()-60 becomes TRUE etc.

It doesn't apply when the workbook is opened it takes effect whenever the
workbook calculates


Mike