How to highlight the date outside the deadline in Excel? You can use the standard date’s rules or create your own custom rules.

Internal Conditional formatting date rules

Excel has some date rules already recorded in the conditional formatting menu.

In the menu Home > Conditional formatting > Highlight Cells Rules > A Date Occurring…, 

You can create the following rules :

• Yesterday
• Today
• Tomorrow
• In the last 7 days
• Last week
• This week
• Next week
• Last month
• This month
• Next month

To apply one of these rules it’s very easy.

1. Select the cells with the dates to highlight
2. Apply one of the rules (here, this week)

But with those standard rules there are many problems.

• The number of rules is very limited
• Not possible to create a rule greater than or lower than
• All the rules are based on the date of Today
• Not possible to include a variable like the x days before today

So, this dialog box is not very useful to highlight dates

Create your custom rule

The best technique when you want to highlight dates, it’s to build your custom rule.

To do that, you must create your own logical test.

Highlight Dates outside the deadline

In case you want to highlight all the dates outside the deadline, we must build a logical test with the function TODAY.

In our example, we can create the following test

=C2<TODAY()

To visualize the result of each test, you can write it in another column

And then, you paste this formula, into the conditional formatting custom rule (Home > Conditional formatting > New Rule > Use a formula to determine which cells to format)

And immediately, all the dates under the date of today (29/06/2022) are in red

Warning few days before to highlight the deadline

Now, we want to add another color. We want to color in orange the dates equal to today and 3 days before the deadline. This time, the test is more complex because we must write the 2 logical tests in an AND function.

=AND(C2>=TODAY(),C2<=TODAY()+4)