Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Conditional Formatting in Excel Help Please..... | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |