Date Span Crossover Formula/Script?

[Jon]

Hi

If anyone has any ideas about this I would be very grateful - I can't quite see a way through this one!

One of the elements of the system I'm building schedules the use of specific tools. I would like to be automatically notified if a tool is double-scheduled, ie. if the duration of a booking clashes with another booking.

As far as I'm aware, there isn't a way of defining timespans in terms of dates in Airtable, other than visually in a timeline or gantt view. The furthest I've got towards a solution is to work with start dates and durations, but I feel like this will get pretty complicated, and would need to include a complex set of IF() formulae that I can't quite fathom out.

If anyone can see a clear way through, or just has any logical ways to progress, I would be very grateful.

I'm also open to scripting solutions if that's the cleanest way forward.

Jon

[TheTimeSavingCo]

If you know someone who can write a script for you, yeah that'd probably be easiest.

If not, you could potentially do this via what I call a "job queue" system, where you set up your base to go through each record one by one to check whether there are any clashes.  Bear with me, it gets pretty weird.

You'd first need to create a new table called "Helper" or something and link all of your records to it, and then use a rollup field with a conditional to output the "Created Time" of the booking record that you want to check clashes for.  (Here's an example base with the rollup max thing set up in case you're not familiar with it)

You'd then have lookup fields in the "Helper" table that would display the "Start" and "End" dates of the booking you're currently checking for clashes.

You'd then have lookup fields in your "Bookings" table that would display the "Start" and "End" dates of the booking you're currently checking for clashes, and you'd have formula fields that would check the "Start" and "End" dates of the record's booking clashed with the "Start" and "End" dates of the booking you're checking

And finally you'd have an automation that would trigger on said formula field that would then link itself to the record that you're checking for clashes for, which you could then use as a reference for "hey these records overlap"

This should (theoretically) work heh.  If you could use some help setting this up I do free half hour consultations and you can book one here, or you could also just hire me to do it too!

[Jon]

Hi Adam

Thank you for all that! I think I get the gist of it, but one question - if there was a clashing booking of a tool where the start and end dates were not the same, eg. a tool booking lasting 8 days with the clashing booking being 1 day in the middle, or one booking starting earlier than the other, would your proposed system pick this up?

Jon

[TheTimeSavingCo]

Yeap it'd catch it fine.  It's really just down to the formulas themselves: given two sets of start end dates, if we can come up with a formula that can check whether there's overlap between the two then the system would work fine

[Jon]

Yes but what would the formulae be... As you say "if we can come up with a formula"...

[TheTimeSavingCo · Accepted solution]

It'd have a bunch of `IS_AFTER()`, `IS_BEFORE()` and `IS_SAME()`s; it's a bit of a pain setting it up but it's doable!

[Jon]

Ah that makes sense! It's an area of formulae I haven't looked into yet. I'll DM you to see if there's a way of us working together...

[MakerAntoinette · Accepted solution]

Hi! I know this is several months old now but I found this thread while looking for the answer to my own question, and I actually have a formula you can use in a larger script if you still need help:

if ((eventStart.valueOf() <= startDate.valueOf() && startDate.valueOf() < eventEnd.valueOf()) || (eventStart.valueOf() < endDate.valueOf() && endDate.valueOf() <= eventEnd.valueOf())){
            overlaps = true
        }

If you don't know scripting, this basically says:

If [Start Date of Scheduled Event] ≤ [Start Date of Event I Want to Schedule] < [End Date of Scheduled Event] OR [Start Date of Scheduled Event] < [End Date of Event I Want to Schedule] ≤ [End Date of Scheduled Event], then the event I want to schedule overlaps.

Even more basically, this checks if the start date OR end date of the event I want to schedule is between the start and end of an existing event. Note that all the variable names (eventStart, eventEnd, startDate, endDate, overlaps) are specific to my program and you could make them whatever you want. 

[Jon]

Hi Antoinette 

Thanks for sharing this! I'll need to digest/integrate it but it looks super neat

Jon