How to Highlight Dates Based on Due Date in Excel

In this trick we will be teaching you how to Highlight Dates Based on Due Date in Excel. With conditional formatting, you can easily create a “past due” report highlighting overdue items. The example shown demonstrates a scenario where the dates that are more than 90 days overdue. (Highlight Dates Based on Due Date)

About Microsoft Excel: (Highlight Dates Based on Due Date)

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA). It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of the Microsoft Office suite of software.

Step by Step Guide to Highlight Dates Based on Due Date in Excel

Step 1:
Select the data cells in your target range (cells C4:C9 in this example), click the Home tab of the Excel Ribbon, and then select Conditional Formatting → New Rule.

Step 2: (Highlight Dates Based on Due Date)
In the list box at the top of the dialog box, click the Use a Formula to Determine which Cells to Format option.
* This selection evaluates values based on a formula you specify. If a particular value evaluates to TRUE, the conditional formatting is applied to that cell.

Step 3:
In the formula input box, enter the formula shown here. In this formula, you evaluate whether today’s date is greater than 90 days past the date in your target cell (C4). If so, the conditional formatting will be applied.

Step 4:
Click the Format button.
* This opens the Format Cells dialog box, where you have a full set of options for formatting the font, border, and fill for your target cell. After you have completed choosing your formatting options, click the OK button to confirm your changes and return to the New Formatting Rule dialog box.

Step 5: (Highlight Dates Based on Due Date)
Back in the New Formatting Rule dialog box, click the OK button to confirm your formatting rule.
* If you need to edit your conditional formatting rule, simply place your cursor in any of the data cells within your formatted range and then go to the Home tab and select Conditional Formatting → Manage Rules. This opens the Conditional Formatting Rules Manager dialog box. Click the rule you want to edit and then click the Edit Rule button.