Conditional formatting dates due to expire

  • Thread starter Thread starter Allan Skyner
  • Start date Start date
A

Allan Skyner

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
 
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
 
Back
Top