How to Highlight Dates Based on Due Date in Excel

In this article, we will learn how to use conditional formatting to highlight dates based on due date in Excel. This is a useful technique to visually identify tasks that are overdue, due soon, or completed on time.

Conditional formatting is a feature in Excel that allows you to apply different formats (such as colors, fonts, icons, etc.) to cells or ranges based on certain criteria or rules. You can use conditional formatting to highlight cells that meet a specific condition, such as being greater than, less than, equal to, or between a certain value.

In our example, we will use conditional formatting to highlight dates that are:

• Red, if the date is past the due date
• Yellow, if the date is within 7 days of the due date
• Green, if the date is on or after the due date

Steps

To apply conditional formatting to highlight dates based on due date, follow these steps:

1. Select the range of cells that contain the dates you want to highlight. In our example, we will select cells B2:B11.
2. On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.
3. In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format.
4. In the Format values where this formula is true box, enter the formula that defines the condition for the first color. In our example, we will enter =B2<$C$2 to highlight dates that are past the due date in red. Note that we use absolute references ($C$2) for the due date cell, so that the formula applies to the entire range.
5. Click Format, and then in the Format Cells dialog box, choose the format you want to apply. In our example, we will choose a red fill color.
6. Click OK to close the Format Cells dialog box, and then click OK to close the New Formatting Rule dialog box.
7. Repeat steps 2 to 6 for the other two colors, using the following formulas and formats:

• For yellow, use the formula =AND(B2>=$C$2,B2<=$C$2+7) and choose a yellow fill color.
• For green, use the formula =B2>$C$2+7 and choose a green fill color.

8. Click OK to apply the conditional formatting to the selected range.

Example

Here is an example of how the conditional formatting looks like in Excel, using some sample data and a due date of February 28, 2024.

As you can see, the dates that are past the due date are highlighted in red, the dates that are within 7 days of the due date are highlighted in yellow, and the dates that are on or after the due date are highlighted in green.

Alternative Methods

There are other ways to highlight dates based on due date in Excel, such as using built-in conditional formatting rules, icons sets, or data bars. Here are some examples of how to use these methods:

• To use built-in conditional formatting rules, select the range of cells that contain the dates, and then on the Home tab, in the Styles group, click Conditional Formatting, and then click Highlight Cells Rules, and then choose the rule you want to apply, such as Greater Than, Less Than, Between, or A Date Occurring. You can then specify the value or date and the format you want to apply.
• To use icon sets, select the range of cells that contain the dates, and then on the Home tab, in the Styles group, click Conditional Formatting, and then click Icon Sets, and then choose the icon set you want to apply, such as 3 Arrows, 3 Flags, or 3 Traffic Lights. You can then adjust the icon criteria and the values by clicking Manage Rules, and then editing the rule you applied.
• To use data bars, select the range of cells that contain the dates, and then on the Home tab, in the Styles group, click Conditional Formatting, and then click Data Bars, and then choose the data bar style you want to apply, such as Solid Fill or Gradient Fill. You can then adjust the data bar options and the values by clicking Manage Rules, and then editing the rule you applied.