Many applications track dates-for deliveries, project management, appointments, and so on. When working with dates, you might want an alert as the date approaches or passes. Using conditional formatting you can format the date to draw your attention to the record.
Now suppose you keep a simple sheet (below) of delivery dates for your product and you want Excel to display today’s delivery records. To set this formatting, you’d do the following:
- Select the data range. In the example, that’s C4:C11.
- Click the Home tab.
- In the Styles group, click Conditional Formatting.
- Choose New Rule.
- In the resulting dialog box, choose Format Only Cells That Contain in the upper pane.
- In the lower pane, choose Dates Occurring from the first dropdown.
- In the second dropdown, choose Today.
- Click Format and then click the Font tab.
- From the Color dropdown, choose Red, and click OK twice.
As you can see, the conditional formatting displays the current date in red (assuming the current date is November 15.) Using the Dates Occurring settings, you can easily create conditions for just about any situation.
If you want to format more than the date cells, or you’re using Excel 2003, you’ll need a formula. In this case, you’d use the formula, =$C4=TODAY(). To highlight yesterday, you’d use the formula =$C4=TODAY()-1; to highlight tomorrow, you’d use the formula =$C4=TODAY()+1, and so on. (In Excel 2003, Conditional Formatting is on the Format menu.)