Excel Tutorials: How to Use Conditional Formatting Dates in Excel

Excel 2007 Conditional Formatting for a Date

For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series.


Adding conditional formatting to a cell in Excel allows you to apply different formatting options, such as color, to a cell or cells that contain dates.
Excel 2007 allows you to easily add formatting so that a date that has past - such as overdue bills is easily spotted. Similarly, a date in the future can also be highlighted using different background and/or font colors.
Example: Adding conditional formatting to a date in Excel
Note: For help with this example, see the image above.

1. Click on cell C1 to make it the active cell.


2. Click on the Home tab.


3. Click on the Conditional Formatting icon on the ribbon to open the drop down menu.


4. Choose Highlight Cell Rules > A Date Occurring... to open the conditional formatting dialog box.


5. Click on the down arrow of the date options drop down menu (left side) to open the list.


6. Select In the last 7 days from the list.


7. Click on the down arrow of the format options drop down menu (right side) to open the list.


8. Select Light Red Fill with Dark Red Text from the list.


9. Click OK to accept the changes and close the dialog box.


10. Type yesterday's date in cell C1 and press the ENTER key on the keyboard.


11. Cell C1 should be formatted with a light red background color and dark red text since yesterday's date occurred within the last seven days.

Note: While it is possible to edit the formatting that is applied with conditional formatting, it is not possible to edit the date options using the Manage Rules option in the Conditional Formatting drop down list.