Identify overlapping dates and times in Excel

I often get asked how to identify overlapping dates and times in Excel. Excel doesn’t have an ‘overlap’ function, but with SUMPRODUCT we can identify dates or times that fall within the same range. For example, let’s say you have list of appointments scheduled for the month. If you plot them visually in a calendar layout you can see that appointments B and C overlap, as do appointments E and F:

But how do you identify overlapping dates if you just have a table of start and end dates:

Watch the Video

Download Workbook

Formula to Identify Overlapping Dates and Times in Excel

With SUMPRODUCT we can check if each start date is less than any of the end dates in the table AND, if each end date is greater than any of the start dates in the table. If the dates on each row meets this criteria for more than one set of dates in the table, then we know there are overlapping dates.

It returns TRUE if the date range overlaps with another date range in the table:

It works the same with times:

Note: my data is structured in an Excel Table, therefore the cell references use the Table’s structured references.

Evaluating each component of the formula returns the following series of true and false results:

We can see in the first row of the table above that the start and end dates only match those on the first row (row 7), making this date range unique in the table. However, in the second row of the table shown below we can see there are two rows that match the dates on row 8:

And when you multiply true and false by one another, Excel converts them to their numeric equivalent or 1 and 0, resulting in this for the first row:

And this for the second row:

SUMPRODUCT returns the sum of the array and if it is greater than 1, it means there are two or more date ranges that overlap.

Identify overlapping dates and times in grouped data

If your data table contains groups, for example, multiple meeting rooms and you want to identify if each meeting room has an overlap, you can use the FILTER function to return the list of start/end times for that particular meeting room, as shown below:

The FILTER function returns the list of start and end times specific to each meeting room. Taking meeting room A as an example, it returns the following:

=SUMPRODUCT(

([@[Start Time]]<{8:30, 9:00, 11:00, 11:30})*

([@[End Time]]>{7:30, 8:30, 9:30, 10:30})

)>1