IFs, COUNTIFS, and logic operators

Chiu Bar

Hello,

I've been working on a scheduler for beach volleyball reservations with the following key functionalities:

1. Each reservation includes a selected date. From this, a simplified date value (month + day) is extracted. A conflict checker then identifies if another booking exists on the same date.

2. Both Start Time and End Time are recorded and automatically converted to a 24-hour format for consistency and easier conflict validation. It is coupled with a functionality that determines if each reservation is at a minimum duration of 3 hours, ensuring sufficient playtime per booking.

3. With the helper columns in place, I was able to develop a COUNTIFS formula to identify potential conflicts by checking whether any existing booking shares the same start time or falls within the same time period as the current booking:

=IF(

AND([Conflict Checker: Date Picker]@row = "This booking shares a date with another existing booking.", [Duration : Court No. 1]@row = "3 hrs or more"),

IF(COUNTIFS([Date Value]:[Date Value], @cell = [Date Value]@row , [Duration : Court No. 1]:[Duration : Court No. 1], @cell = "3 hrs or more", [Court No. 1 Start Time : 24-hour Value]:[Court No. 1 Start Time : 24-hour Value], OR(@cell = [Court No. 1 Start Time : 24-hour Value]@row , @cell < [Court No. 1 End Time : 24-hour Value]@row )) > 1,

"This booking shares a date & time with another existing booking.", "This booking shares a date with another existing booking but not with time."),

"No other bookings share this date & time.")

=================================================

Test results:

1. First two bookings was determined to have the same time:

2. (Testing further) The first two bookings were determined to be within the same time period with each other:

3. However, when the first two bookings fall on the same date but have non-overlapping time periods, they should not be flagged as conflicts.

But currently, the formula may still return a conflict because it only checks for the same date without considering whether the time ranges actually overlap.

4. Moreover, while the first two bookings should be acceptable—even if they fall on the same date but have different time periods—it is flagged differently by the formula.

Have I wrongly used the logic operations on COUNTIFS?
How can we accurately determine if one booking’s time frame overlaps with another or not?

Any help will be greatly appreciated.

Thank you.

Chiu Bar

Hello @Paul Newcome ,

Thank you for your input.

I was able to dissect and came up with this equation:

=COUNTIFS([Court No. 1 End Time : 24-hour Value]:[Court No. 1 End Time : 24-hour Value], @cell > [Court No. 1 Start Time : 24-hour Value]@row , [Court No. 1 Start Time : 24-hour Value]:[Court No. 1 Start Time : 24-hour Value], @cell < [Court No. 1 End Time : 24-hour Value]@row )

It's spot on!

Paul Newcome

Try something more along these lines (update column names and outputs as needed),

=IF(COUNTIFS([Date Value]:[Date Value], @cell = [Date Value]@row, Duration:Duration, @cell = "3 hrs or more", [24hr Start Time]:[24hr Start Time], @cell < [24hr End Time]@row, [24hr End Time]:[24hr End Time], @cell > [24hr Start Time]@row) > 0, "Conflict Message", IF(COUNTIFS([Date Value]:[Date Value], @cell = [Date Value]@row) > 1, "Shared w/no conflict", "No other bookings today"))

Chiu Bar

Hello @Paul Newcome ,

Thank you for your input.

I was able to dissect and came up with this equation:

=COUNTIFS([Court No. 1 End Time : 24-hour Value]:[Court No. 1 End Time : 24-hour Value], @cell > [Court No. 1 Start Time : 24-hour Value]@row , [Court No. 1 Start Time : 24-hour Value]:[Court No. 1 Start Time : 24-hour Value], @cell < [Court No. 1 End Time : 24-hour Value]@row )

It's spot on!

Paul Newcome

Happy to help. 👍️