Conditional Formatting

L

LtWesterholm

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.
 
J

Jason Morin

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
 
D

Duke Carey

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
 
D

Duke Carey

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
 
Top