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
Enter your email address below to download the sample 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 
Need a formula to identify overlapping dates and times in a spreadsheet where each row has its own Meeting Room, DateFrom, DateTo, TimeFrom, TimeTo columns, we need to identify where the times are overlapping within the dates being evaluated for the meeting room in that row, not merely the dates alone regardless of the time or the time alone regardless of the date.
Hi Nat, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Awesome, thank you! Any idea how we can do something similar to the FILTER in non-365 versions of Excel?
Not easily. You’re welcome to post your question on our Excel forum where you can also upload a sample file and we can help you further. Be sure to also include the version of Excel that you have so we know the limitations.
Thank you for the informative information. And how exactly do you calculate the overlapping time period (in hours and minutes) between two different time frames?
I’m not sure what you mean by ‘locate’. Perhaps you can post your question on our Excel forum where you can also upload a sample file and we can help you further.