Highlight the Date outside the deadline in Excel

Highlight the Date outside the deadline in Excel
Last Updated on 12/05/2023
Reading time: 2 minutes

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..., 

Menu conditional formatting Date

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
Conditional Formatting Date rules

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)
Use of the standard conditional formatting date rule

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

Add a column with the logical test on Today

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)

Convert the logical test into a conditional formatting rule

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

All the date outside the deadline are highlighted

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)

Warning when the dates are close to the deadline

Leave a Reply

Your email address will not be published. Required fields are marked *

Highlight the Date outside the deadline in Excel

Reading time: 2 minutes
Last Updated on 12/05/2023

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..., 

Menu conditional formatting Date

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
Conditional Formatting Date rules

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)
Use of the standard conditional formatting date rule

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

Add a column with the logical test on Today

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)

Convert the logical test into a conditional formatting rule

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

All the date outside the deadline are highlighted

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)

Warning when the dates are close to the deadline

Leave a Reply

Your email address will not be published. Required fields are marked *