Automatically highlight upcoming and past due dates

From Excel 2010, there’s a way to automatically highlight upcoming and past due dates. For example, if you have expiry dates for stock in a spreadsheet, it’s possible to highlight the stock that has expired as well as the stock that will expire within 30 days. This is achievable with conditional formatting. Below is a demonstration of how this can be done.

Join Excel List

You’re also welcome to download the workbook to practice.

1. Select the Home tab in the toolbar at the top of the screen.

2. Then in the styles group, click on the Conditional Formatting drop-down and select Manage Rules.

tip1

3. When the Conditional Formatting Rules Manager window appears, click on the “New Rule” button to enter the first condition.

tip2

4. When the New Formatting Rule window appears, select the “Format only cells that contain” rule type.

Then select Cell Value in the first drop down, less than in the second drop down, and enter the following formula:

=NOW()+30

Next, we need to select what formatting to apply when this condition is met. To do this, click on the Format button:

tip3 

5.  When the Format Cells window appears, select the Fill tab. Then select the color that you would like to use for the dates that will expire in the next 30 days. In this example, we’ve selected green. Then click on the OK button.

tip4

6.  When you return to the New Formatting Rule window, you should see the preview of the formatting in the preview box. In this example, the preview box shows green as the fill color. Click on the OK button.

tip5

This will return you to the Conditional Formatting Rules Manager window. Select OK.

tip6

The stock that has already expired, as well as the stock that will expire in less than 30 days from now, will be highlighted in green.

tip7

This is an easy and effective way of automatically highlighting upcoming and past due dates in Excel.


  • monica

    can I apply more than one rule to the same cells. if I want a notification for 90, 60 and 30 days?

    • coll

      yes I would like a rule on 30,60 and 90 days past due

  • Edwin

    Hi,
    Thanks for your question.
    • Apply the steps in the tip twice and change the number of days under steps 4 and 6.
    o First to 60 days
    o Then to 90days

Sage © Sage South Africa Pty Ltd 2016 . All Rights Reserved.