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

Sam Swain · 11/20/24

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 · 11/20/24

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 · 11/20/24

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

Courtney S. · 11/22/24

Smartsheet doesn't have a way to make a booking system that prevents overlaps automatically, but there is some advice you may find helpful in these posts:

Booking System in Smartsheets Forms — Smartsheet Community

Meeting Room Booking System — Smartsheet Community

Need formula to indicate double booking on the same date. — Smartsheet Community

Want to flag overlapping dates AND times — Smartsheet Community

Flagging a Date and Time Overlap — Smartsheet Community

You could create a separate sheet that is populated with formulas like Countifs, Index+Collect and/or Join+Collect to see what date & time ranges have more than one value? Eg. one row per date, one column per hour of the day, formulas looking at the main sheet to search the rows containing that day+time value. You'd want Collect or Countifs so you can provide multiple criteria looking at multiple columns.

COUNTIFS Function | Smartsheet Learning Center

INDEX Function | Smartsheet Learning Center

JOIN Function | Smartsheet Learning Center

COLLECT Function | Smartsheet Learning Center

Formula combinations for cross sheet references | Smartsheet Learning Center

Sam Swain · 11/22/24

Very helpful, thank you everyone!

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

Answers

Help Article Resources

Categories