Google Sheets Scheduling Overlap

Question

I'm trying to make a schedule that will see if a room is double booked. If it is, the conflicting cells will highlight.

If there are duplicates of the same date, I want it to check to see if the same room appears in both rows. If it does, check against for overlapping times. If that's true, then highlight the conflicting rows.

Here is my sample sheet: https://docs.google.com/spreadsheets/d/1XxBTbNAHkYOaOXdh487L9jp7MSWLYsEaqlYqZsN3P2E/edit?usp=sharing

Thank you.

Answer 1

I believe I've figured it out:

=SUMPRODUCT(($A2=$A:$A)*($D2=$D:$D)*($B2<$C:$C)*($C2>$B:$B))>1