Overlapping Date Ranges

[Celina_Fazio]

Hi Coda Community,

I know there have been some threads about this already, but I’m having a hard time getting the solutions to work for my purposes. I’m creating a booking request system that limits how many occurrences can overlap. (For context, the events will have equipment being used, of which we have limited inventory).

I’m looking to create a formula to put a restriction on the number of overlapping date ranges in a column. We need to limit the amount of overlapping date ranges to 5, so that on the 6th occurrence, it’s flagged/highlighted/restricted.

The formula would need to capture any overlap, regardless of the number of dates that overlap. It doesn’t need to necessarily need to identify which dates overlap, but rather if there are any that do. It should also be assumed that the column’s order will represent the order in which requests to book have been submitted.

In the example below:

• Events 9 and 12 would need to be flagged.
• Despite Event 12 only overlapping congruently with Events 6, 7, and 8, it shares dates within the range of Events 10 and 11.
• The start date of Event 12 would have to occur after the earliest free day of the other 5 events (in this case, after July 13th of Event 10).

I hope this makes sense! Please let me know if I didn’t explain anything properly.

Appreciate any help.

[Jannis]

Is it possible to share the doc in play-mode for better understanding? @Celina_Fazio

[Christiaan_Huizer]

hi @Celina_Fazio ,

my contribution below. Event 9 has overlap as you can see.

code step 01
thisTable.Filter([Start date] >= thisRow.[Start date] and [End date] <= thisRow.[End date])

code step 02
thisTable.Filter([Start date] >= thisRow.[Start date] and [End date] <= thisRow.[End date]).Nth(5)

I hope it helps to find the solution you have in mind, Cheers, Christiaan

[Celina_Fazio]

Have at it!