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.
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?
HansVogelaar Sure.
Name Date Start Time Stop Time Type Changed 4/5/2023 927 1035 I Changed 4/5/2023 1015 1100 I Changed 4/5/2023 1530 1555 I Changed 4/10/2023 0830 0845 I Changed 4/10/2023 1330 1425 I Changed 4/10/2023 1435 1535 I Changed 4/11/2023 1455 1540 I Changed 4/12/2023 1145 1245 I Changed 4/12/2023 1245 1345 I Changed 4/14/2023 1430 1520 I Changed 4/20/2023 1045 1105 I Changed 4/20/2023 1115 1205 I Changed 4/26/2023 830 0930 I Changed 4/26/2023 0930 1030 I 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=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)
