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