Forum Discussion
Overlapping Times in Excel
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.
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
=COUNTIFS($A$2:$A$5000,A2,$B$2:$B$5000,B2,$C$2:$C$5000,"<"&D2,$D$2:$D$5000,">"&C2,$E$2:$E$5000,E2)>1
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
=COUNTIFS($A$2:$A$5000,A2,$B$2:$B$5000,B2,$C$2:$C$5000,"<="&D2,$D$2:$D$5000,">="&C2,$E$2:$E$5000,E2)
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.
