Formula for 24hour clock overlap - booking clashes at the same time on the same day

Sam Swain

Hello all,

I am hoping someone out there has a smart idea of how to pick up on reservations overlapping within a 24hour time period.

In this instance, there are two people parking in same bay on the same day. Formula I have is:

=COUNTIFS([Date Of Parking]:[Date Of Parking], [Date Of Parking]@row, [Bay Number]:[Bay Number], [Bay Number]@row))

Result here is 2, indicating a clash when in fact it is not a clash because the start / end time do not overlap.

I want to extend this formula / find a different formula / use helper columns / or any other way to register that the start time and end time do not crossover, so therefore this is not a clash.

Many thanks in advance for your time!

ChristianFinke

Hi @Sam Swain

To find the true booking overlaps, you can use a helper column with a formula that checks for the same date, bay, and overlapping times. I would try the below formula

=IF(AND([Date Of Parking]@row = [Date Of Parking]1, [Bay Number]@row = [Bay Number]1, OR([Start Time]@row < [End Time]1, [End Time]@row > [Start Time]1)), "Clash", "No Clash")

Sam Swain

Hi Christian, thanks for answering! That works for the instance above…

But how do I check for an entire column of entries with different start and finish times?

For instance, the result below returns no clash, but the times overlap

I guess I need an entry to compare itself to all the other start finish time ranges to see if it overlaps with any other booking on the same in the same bay