Conditional Formatting
 

How do I set up cells to identify expiration dates? For instance, on a
matrix I identify the date someone receives a 2-year certification. Eighteen
months later, I would like the date to change to amber. At the expiration
date, I would like the cell to turn red.

Assuming the date is in A1, select A1, go to Format
Conditional Formatting, choose "Formula Is" and put:

=TODAY()=DATE(YEAR($A$1),MONTH($A$1)+18,DAY($A$1))

Press the Format radio button, click the Pattern tab, and
choose something close to Amber. Press OK.

Click the Add button, choose Formula Is, and insert this:

=TODAY()=DATE(YEAR($A$1),MONTH($A$1)+24,DAY($A$1))

Follow the same steps (except choose red this time).

To learn more about CF, see:

http://www.contextures.com/xlCondFormat01.html

HTH
Jason
Atlanta, GA

-----Original Message-----
How do I set up cells to identify expiration dates? For
instance, on a
matrix I identify the date someone receives a 2-year
certification. Eighteen
months later, I would like the date to change to amber.
At the expiration
date, I would like the cell to turn red.
.

Select the cell with the date
From the Format menu choose Conditional Formatting

In the main dialog box:
in the left hand drop down choose "Cell Value Is"
in the middle drop down choose "less than or equal to"
in the text box type =NOW()-730
Click on the format button and choose a Red pattern and click on OK

In the main dialog box click on the Add button
in the left hand drop down choose "Cell Value Is"
in the middle drop down choose "less than or equal to"
in the text box type =NOW()-547
Click on the format button and choose an Amber pattern and click on OK

Click on OK


"LtWesterholm" wrote:

How do I set up cells to identify expiration dates? For instance, on a
matrix I identify the date someone receives a 2-year certification. Eighteen
months later, I would like the date to change to amber. At the expiration
date, I would like the cell to turn red.

May take a hybrid of Jason's suggestion and mine

In the main dialog box:
in the left hand drop down choose "Cell Value Is"
in the middle drop down choose "less than or equal to"
in the text box type

=DATE(YEAR(today())-2,MONTH(today()),DAY(today()))

Click on the format button and choose a Red pattern and click on OK

In the main dialog box click on the Add button
in the left hand drop down choose "Cell Value Is"
in the middle drop down choose "less than or equal to"
in the text box type

=DATE(YEAR(today()),MONTH(today())-18,DAY(today()))

Click on the format button and choose an Amber pattern and click on OK



"Duke Carey" wrote:

Select the cell with the date
From the Format menu choose Conditional Formatting

In the main dialog box:
in the left hand drop down choose "Cell Value Is"
in the middle drop down choose "less than or equal to"
in the text box type =NOW()-730
Click on the format button and choose a Red pattern and click on OK

In the main dialog box click on the Add button
in the left hand drop down choose "Cell Value Is"
in the middle drop down choose "less than or equal to"
in the text box type =NOW()-547
Click on the format button and choose an Amber pattern and click on OK

Click on OK


"LtWesterholm" wrote:

How do I set up cells to identify expiration dates? For instance, on a
matrix I identify the date someone receives a 2-year certification. Eighteen
months later, I would like the date to change to amber. At the expiration
date, I would like the cell to turn red.