Conditional formatting date past due

To highlight dates that are "past due" you can use a conditional formatting rule that checks if the variance between two dates is greater than a certain number of days. In the example shown, three conditional formatting rules have been applied to the range D5:C12 with these formulas:

=(D5-C5)<3 // rule 1 (green)
=(D5-C5)<10 // rule 2 (yellow)
=(D5-C5)>=10 // rule 3 (red)

Note: conditional formatting rules are evaluated relative to the upper left cell in the selection at the time the rule is created, in this case D5.

Calculating variance in days

The variances in column E are calculated by subtracting the original date from the current date with this formula:

=D5-C5

The result is the difference in days between the original date and the current date. A positive difference represents a late date (i.e. a "slip" in the schedule). A negative difference indicates the current date is ahead of schedule. This works, because Excel dates are serial numbers.

The variance shown in column E is for reference only in this example, and is not used by the conditional formatting rules. However, if you treat column E as a helper column, you could write simpler conditional formatting rules that use the variance directly.

Explanation

In this example, we want to apply three different colors, depending on how much the original date varies from the current date:

Green if the variance is less than 3 days
Yellow if the variance is between 3 and 10 days
Red if the variance is greater than 10 days

For each rule, we calculate a variance by subtracting the original date from the "current" date (as explained above). Then we check the result with a logical expression. When an expression returns TRUE, the conditional formatting is triggered.

Because we want three separate colors, each with a logical test, we'll need three separate conditional formatting rules. The screen below shows how the rules have been configured to apply the green, yellow, and red formatting. Note the first two rules have "stop if true" ticked:

Rules for formatting dates past due

Rules are evaluated in the order shown. Rule 1 tests if the variance is less than 3 days. Rule 2 checks if the variance is less than 10 days. Rule 3 checks if the variance is greater than or equal to 10 days. Both rule 1 and rule 2 have "stop if true" enabled. When either rule returns TRUE, Excel will stop checking additional rules.

Overdue by n days from today

You might want to compare a due date to today's date. To test if dates are overdue by at least n days from today, you can use a formula like this:

=(TODAY()-date)>=n

This formula will return TRUE only when a date is at least n days in the past. When a date is in the future, the difference will be a negative number, so the rule will never fire.

For more information on building formula criteria, see 50+ formula criteria examples.