Forum Discussion
Highlight exam sessions that clash
I'm an Exams Manager at a college, and I need to find a way of identifying exams that clash with each other on Excel. i.e. - Joe Bloggs has Biology and French at 0900 on 08/05/2023, it highlights them as a clash/match. I would then go on our MIS system to change the start/finish times on one of the exams so they no longer clash. This is what my timetable looks like, so it would need to flag ones where a person code has two exams at the same exam date/time.
I've tried doing it through conditional formatting but not getting anywhere. I was going to merge the person code, date and time into one cell and then do it, but the formatting wont allow that.
1 Reply
Select rows 2 to 100, or as far down as the data go.
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$1000, $A2, $T$2:$T$1000, $T2, $U$2:$U$1000, "<="&$V2, $V$2:$V$1000, ">="&$U2)>1Click Format...
Activate the Fill tab.
Select a highlight color, for example red.
Click OK, then click OK again.Conflicting rows will be highlighted
