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

Explanation

Conditional formatting rules are evaluated in order.

  1. For each cell in the range B5:B12, the first formula is evaluated. If the value is greater than or equal to 90%, the formula returns TRUE and the green fill is applied. If the value is not greater than or equal to 90%, the formula returns FALSE and the rule is not triggered.
  2. For each cell in the range B5:B12, the second formula is evaluated. If the value is greater than or equal to 80%, the formula returns TRUE and the yellow fill is applied.
  3. For each cell in the range B5:B12, the third formula is evaluated. If the value is less than 80%, the formula returns TRUE and the pink fill is applied.

The “stop if true” option is enabled for the first two rules to prevent further processing, since the rules are mutually exclusive.