I need help creating a system that can identify when conference rooms are booked twice for the same time slot. When this happens, I want the rows with conflicts to be highlighted automatically.
My idea is to check if there are multiple entries for the same date first. Then I want to see if the same conference room appears in those entries. Finally, if the time periods overlap between those bookings, the system should highlight both rows to show there’s a problem.
I’m working on a booking system where people can reserve rooms and I need to catch these double bookings before they cause issues. Has anyone built something similar or know what formulas might work for this?
pivot tables are super helpful for this! just set up rooms as rows, dates as columns, and booking count as values. any cell with 2+ shows a conflict. way easier than dealing with all the formulas. this worked wonders for our lab schedule!
Had this exact problem with our office booking system last year. Here’s what worked: I made a helper column that combines room name + date + time range into one string. Then COUNTIF checks if that combo shows up more than once. For time overlaps, I converted start/end times to decimals and used a formula checking if booking A starts before booking B ends AND booking A ends after booking B starts. Catches every overlap scenario. The annoying part? People entered times in different formats, so I had to convert everything to 24-hour format first. Once it’s set up, conditional formatting highlights conflicts automatically. Total game-changer - no more double bookings.
I did something similar with conditional formatting and array formulas. Create a composite key that handles the overlap logic right in the formatting rule. I used SUMPRODUCT to count other rows with the same room AND date AND where max start time < min end time. Catches overlaps without messy helper columns cluttering your sheet. The formula gets messy but it’s worth it - users just see clean highlighting when they mess up. Big gotcha: keep your time formats consistent or everything breaks. Also, you might want to add buffer time between bookings for transitions - you can build that right into the formula.