How to calculate if two dates fall within a specific day

[gwitud]

I’m really struggling on working out how to create a formula for the above. For context, I have an AirBNB type operation whereby there is a check in + check out date for guests who stay which can sometimes be as long as a few weeks.

I’m trying to figure out the best way to work out if a specific day falls between 2 dates so I can group data based on events & also calculate occupancy in a given month or event based on a property (e.g. if there’s a major event in a city on the 10th of February & there’s a checkin on 8th February & checkout on the 11th February, how to have that filter in as an “event booking”). Alternatively, with the same logic, if I want to separate “weekend bookings” from “midweek booking”, I’m happy to have a separate table where I manually input every weekend/event.

Would greatly appreciate any ideas anyone might have on how to solve it

[AirtableBuilds_ · Accepted solution]

Hey there,

This is definitely a tricky one! And before providing a potentially over-complicated solution, would leveraging the calendar multiple date function work for you? It won’t provide you a field that specifically states what event overlaps the booking, however it will be able to visually represent this really well!

Simple Solution

For example, have just one table that has all your Airbnb Bookings and External Events and differentiate between them by labelling each with a “Record Type” single select field.

Then the Calendar view will look like this and you will be able to see any Events and Bookings that overlap.

If however that’s not going to cut it… then here is a more complex solution.

Firstly, like you mentioned you will need to create a new table that houses all of your Event Dates. However, there can only be one record per date. So what I would recommend is pre-creating all the records/dates you need, as well as editing the Table Permissions to prevent record creation/deletion until you need to add more dates (You will still be able to add Events to each date) And you will get something like this:

Next go back to your Bookings table and create a “Date Span” formula field to give you an array of all the unique dates between check in and check out. I used @W_Vann_Hall 's brilliant formula for this, you can see the full details and explanation in the original post here

Then just create a Linked Record between the 2 tables, and copy and paste the “Date Span” field into the “Linked Event Dates” field.

Lastly create a rollup field to show unique Event Names in your Booking table.

Of course, if you want to be super slick, you can always automate this copy+paste job with an Automation

One last thing to keep in mind, if your booking dates change be sure to then clear out the “Linked Event Dates” cell and re-copy+paste the new “Date Span” values into it

Hope that helps!
Una