Dates & Time
Detect Schedule & Room Double-Booking Conflicts (COUNTIFS Overlap)
Flag overlapping conference-room, vehicle, or shift reservations on the same resource using De Morgan's interval overlap rule COUNTIFS(Resource, A2, Start, "<" & C2, End, ">" & B2) - 1 > 0.
Catch overlapping reservations while allowing back-to-back handoffs!
Two bookings [Start_A, End_A] and [Start_B, End_B] on the same room, aircraft, or technician conflict if and only if Booking B starts BEFORE Booking A ends (Start_B < End_A) AND Booking B ends AFTER Booking A starts (End_B > Start_A)! Notice the strict inequalities < and >: if Meeting 1 runs 09:00–10:00 (hours 9 to 10) and Meeting 2 starts at 10:00–11:00, 10 < 10 is FALSE—so back-to-back handoffs do NOT trigger false conflict alarms!
Across an entire multi-resource reservation table, =COUNTIFS($A$2:$A$5, A2, $B$2:$B$5, "<" & C2, $C$2:$C$5, ">" & B2) - 1 counts how many OTHER rows overlap row 2 (subtracting 1 because every valid booking overlaps itself): 0 means Clear, while >= 1 flags a Double-Booked Conflict!
| Resource (A) | Start_Hour (B) | End_Hour (C) | Other Overlaps & Status |
|---|---|---|---|
| Room-A | 9.0 (09:00) | 11.0 (11:00) | 1 overlap (with 10:30–12:00) -> Conflict! |
| Room-A | 10.5 (10:30) | 12.0 (12:00) | 1 overlap (with 09:00–11:00) -> Conflict! |
| Room-A | 12.0 (12:00) | 13.5 (13:30) | 0 overlaps (Back-to-back at 12.0 is Clear!) |
| Room-B | 10.0 (10:00) | 11.5 (11:30) | 0 overlaps (Different room Room-B!) |
Double-Booking Conflict Status Formula (Back-to-Back Safe)
Validates End > Start (C2 > B2), then checks whether COUNTIFS finds more than 1 matching interval (itself + >=1 conflict) on resource A2.
=IF(C2 <= B2, "Invalid Window (End <= Start)", IF(COUNTIFS($A$2:$A$5, A2, $B$2:$B$5, "<" & C2, $C$2:$C$5, ">" & B2) - 1 > 0, "Conflict", "Clear"))Count Conflicting Reservations Against Row 2
Returns the integer number of other bookings overlapping row 2 on the same resource.
=COUNTIFS($A$2:$A$5, A2, $B$2:$B$5, "<" & C2, $C$2:$C$5, ">" & B2) - 1Try the example
Use the original values or change them to test your understanding.