Explanation
Consider for a moment how overlapping dates work. For a project to overlap the dates of other projects, two conditions must be true:
- The start date must be less than or equal (<=) to at least one other end date and the list.
- 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
Related formulas
Highlight dates between
The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because the reference to B4 is fully relative, it will update as the rule is applied to each cell in the range, and...
Related functions
Related videos
How to apply conditional formatting with a formula
In this video, we'll look at how to use a formula to apply conditional formatting. Formulas allow you to make powerful and flexible conditional formatting rules that highlight just the data you want. Let's take a look. Excel provides a large number of conditional formatting presets, but there are...
Conditional formatting based on a different cell
In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula. Let's take a look. The easiest way to apply conditional formatting is to apply rules directly to the cells you want to format. For example, if I want to highlight the average...
How to highlight rows with conditional formatting
Using conditional formatting, It's easy to highlight cells that match a certain condition. However, it's a little trickier to highlight entire rows in a list that contains multiple columns. In this video, we'll show you how to use a formula with conditional formatting to highlight an entire row in...
How to build a search box with conditional formatting
In this video, we'll look at a way to create a search box that highlights rows in a table, by using conditional formatting, and a formula that checks several columns at once. This is a great alternative to filtering, because you can see the information you're looking for highlighted in context. Let...
