Forum Discussion
Overlapping Times in Excel
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)
- 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.
