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.
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.
Easily conditional format every n row in selection:
Kutools for Excel's Alternate Row / Column Shading utility help you to easily add conditional formatting to every n row in Excel selection.
Download the full feature 30-day free trail of Kutools for Excel now!
- 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!