Explanation

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

  1. Green if the variance is less than 3 days
  2. Yellow if the variance is between 3 and 10 days
  3. 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 - 1

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 .

Explanation

Consider for a moment how overlapping dates work. For a project to overlap the dates of other projects, two conditions must be true:

  1. The start date must be less than or equal (<=) to at least one other end date and the list.
  2. The end date for the project must be greater than or equal to (>=) at least one other start date in the list.

If both of these conditions are true, the project dates must overlap with another project in that list. The SUMPRODUCT function is perfect for this kind of test because it handles array comparisons elegantly. To check a project start date against all end dates, we use this expression:

($C6<=$D$5:$D$9)

To check a project end date against all end dates, we use this expression:

($D6>=$C$5:$C$9)

The resulting arrays of TRUE FALSE values are multiplied by each other inside SUMPRODUCT. This coerces the TRUE and FALSE results into 1s and 0s automatically, so the formula is solved like this:

=SUMPRODUCT({0;1;1;1;1}*{1;1;1;0;0})>1
=SUMPRODUCT({0;1;1;0;0})>1
=TRUE