How to conditional format dates less than/greater than today in Excel?

You can conditional format dates based on current date in Excel. For example, you can format dates before today, or format dates greater than today. In this tutorial, we will show you how to use the TODAY function in conditional formatting to highlight due dates or future dates in Excel in details.

Conditional format dates before today or dates in the future in Excel

Conditional format dates before today or dates in the future in Excel

Let’s say you have a list of date as below screenshot shown. For letting the due dates and the future dates outstanding, please do as follows.

1. Select the range A2:A15, then click Conditional Formatting > Manage Rules under Home tab. See screenshot:

2. In the Conditional Formatting Rules Manager dialog box, click the New Rule button.

3. In the New Formatting Rule dialog box, you need to:

1). Select Use a formula to determine which cells to format in the Select a Rule Type section;

2). For formatting the dates older than today, please copy and paste the formula =$A2<TODAY() into the Format values where this formula is true box;

For formatting the future dates, please use this formula =$A2>TODAY();

3). Click the Format button. See screenshot:

4. In the Format Cells dialog box, specify the format for the due dates or future dates, and then click the OK button.

5. Then it returns to the Conditional Formatting Rules Manager dialog box. And the due dates formatting rule is created. If you want to apply the rule now, please click the Apply button.

6. But if you want to apply the due dates rule and the future dates rule together, please create a new rule with the future date formatting formula by repeating the above steps from 2 to 4.

7. When it returns to the Conditional Formatting Rules Manager dialog box again, you can see the two rules are showing in the box, please click the OK button to start formatting.

Then you can see the dates older than today and the date greater than today are successfully formatted.

Related articles:

• How to conditional format cells based on first letter/character in Excel?
• How to conditional format cells if containing #na in Excel?
• How to conditional format or highlight first recurrence in Excel?
• How to conditional format negative percentage in red in Excel?