Overlapping Times in Excel

Copper Contributor

I am trying to determine if overlapping times exist in a large data set based on name and date. I am having issues with sumproduct, sort, and filter.

5 Replies


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar Sure.

NameDateStart TimeStop TimeType


The data set is more than 5,000 rows, has multiple names, and several types. I need to identify overlaps in time that occur on the same day for the same name, with a Type I. 


Assuming that the data are in columns A to E, with headers in row 1:

Select the data rows, starting with A2:E2.

The active cell in the selection should be in row 2.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula




Adjust the ranges as needed.

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.


P.S. This will not flag directly adjacent time periods, such as the last two rows in your example (one ends at 0930, the other starts at 0930).

If you want to flag those too, change the formula to



Hhhhmmmm . . . that doesn't seem to work. And yes, I am trying to flag start and stop times that are the same.


Here is a demo workbook with your sample data, and a slightly improved conditional formatting rule. You can view/edit it by selecting Conditional Formatting > Manage Rules... on the Home tab of the ribbon.