Overlapping Date Ranges

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.

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

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

1 Like

Have at it!

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.