I have a column in a spreadsheet which lists the various dates a lease will
expire. What I would like to do is to change the format to yellow when the
date is due in 90 days, (in other words give me a 3 month warning), and
change to red when the date is reached and past. All the other dates will be
green to signify lease is still current. Thanking anyone in advance.

Assume dates are in col A

Select col A (A1 active),
then apply CF using "Formula Is" for Conditions 1 to 3 as follows:

Condition 1:
=AND(A1<=TODAY(),A1<>"")
Format > Red fill/white font

Condition 2:
=AND(A1<=TODAY()+90,A1<>"")
Format > Yellow fill

Condition 3:
=AND(A1>TODAY()+90,A1<>"")
Format > Green fill

Click to OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Allan Skyner" wrote:
> I have a column in a spreadsheet which lists the various dates a lease will
> expire. What I would like to do is to change the format to yellow when the
> date is due in 90 days, (in other words give me a 3 month warning), and
> change to red when the date is reached and past. All the other dates will be
> green to signify lease is still current. Thanking anyone in advance.

Select the range of cells to be formatted (I will use A1:A10 as my example)
Use Format | Conditional Formatting from menu
Set the dialog to read:
Formula Is: =TODAY()-A1>=0 then with the Format button on the dialog set
the red text or background as needed
Use the Add button; for second condition
Formula Is =A1-TODAY()<=90 with colour yellow
Add the third condition
Formula Is A1-Today()>90 with colour green

Note: my first cell was A1 so that is why A1 appears in the Formulas, adjust
to match you range
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Allan Skyner" <(E-Mail Removed)> wrote in message
news:F1E22944-BC11-463B-B90D-(E-Mail Removed)...
>I have a column in a spreadsheet which lists the various dates a lease will
> expire. What I would like to do is to change the format to yellow when the
> date is due in 90 days, (in other words give me a 3 month warning), and
> change to red when the date is reached and past. All the other dates will
> be
> green to signify lease is still current. Thanking anyone in advance.