Conditional Formatting

[Guest]

Hi, I am in need of help with the conditional formatting feature. I have a
spreadsheet where dates are entered in for the expiration of insurance, and I
am wanting a way to format where the cell will be highlighted once it pasts a
certain date, so I can know what ones are expired and need to be updated.
For example, if the cell says "yes - 11/30/05", I want to have it highlighted
since it is before 02/01/06. I have tried looking up in the help and trying
things, but I still could not figure it out. Can anybody help with this?

 

[Guest]

Although I have to say I am not sure I understand what you meant by "the cell
says "yes-11/30/05" " here is what you can do in the general case for
conditional formatting:

Assume your date cell (cell A1) says "11/30/05". Let's see the following
cases:
1. You need to compare the cell value with the current date.

Highlight the cell(s), select Format-Conditional Formatting and in the
Conditional Formatting window select "Formual Is". In the formula field that
opens just enter the expression:

= $A1<now( )
and then select the desired formatting using the "Format..." button

2. You need to compare the cell value with a date of another cell in the
Sheet (e.g. B1).

Do the same as in (1) but the formula will be:

= $A1<$B$1

3. You need to compare the cell value with a date that does not appear on
any other cell in the Sheet (although not recommended as in any change you
will have to change all the conditional formatting (e.g. this date is
12/15/2005).

Do the same as in (1) and (2) but the formula will be:

= $A1< DATE(2005;12;15)

Hope it works for you

Markos