Date overlap formula

1

Hello,

I am attempting to create a formula to prevent date overlaps. I am using ‘duration’ instead of ‘end date’ because I’m utilizing the number of days a reservation is booked for a separate table that tracks income. Due to this, I don’t have the end date readily accessible. I’m beginning to think it may be easier to rewire my setup so that I do have an ‘end date’ instead of figuring out the formula to calculate it from ‘duration’.

I haven’t found a great solution for working with ‘duration’, but I’d greatly appreciate a suggestion from anyone who’s approached date overlaps in this way.

2

Start Date + Duration

will give you the end date.

You can check for overlap with an existing reservation as:

Bookings Calendar.CountIf(Start Date < New Start Date  + New Duration OR Start Date + Duration > New Start Date) > 0

where New Start Date, New Duration are for the reservation that we are attempting to add.

You can change the comparison operators to be inclusive depending on your definition of overlap.

3

Posted on the problem some time ago:

@louca.developer is right, you don’t have to rewrite your setup — simply add a column that would calculate end date by adding duration to start date. BTW don’t use CountIf() as it’s not optimized, as the docs say.

1 Like
4

BTW don’t use CountIf() as it’s not optimized, as the docs say.

Hmm, didn’t know that. Can’t find a mention of it in the docs also…

5

This page might help you @louca.developer :blush:

You’ll find this :point_down: somewhere in there :blush:

CountIf, etc...
CountIf, etc...1540×806 118 KB

4 Likes
6

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