# Date overlap formula

**URL:** <https://connect.superhuman.com/t/date-overlap-formula/27040>\
**Category:** Ask the Community\
**Created:** [November 23, 2021, 1:14am UTC](https://connect.superhuman.com/t/date-overlap-formula/27040 "2021-11-23T01:14:04Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Andrew\_Hanson](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/andrew_hanson/32/23843_2.png) [@Andrew\_Hanson](https://connect.superhuman.com/u/Andrew_Hanson)\
**Post date:** [November 23, 2021, 1:14am UTC](https://connect.superhuman.com/t/date-overlap-formula/27040/1 "2021-11-23T01:14:04Z")

</div>

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.

https://coda.io/embed/7yLAMkOpfS/_suqGT?viewMode=embedplay

---

<div class="post-metadata">

**Author:** ![louca.developer](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/louca.developer/32/22685_2.png) [@louca.developer](https://connect.superhuman.com/u/louca.developer)\
**Post date:** [November 23, 2021, 3:10am UTC](https://connect.superhuman.com/t/date-overlap-formula/27040/2 "2021-11-23T03:10:38Z")

</div>

`Start Date + Duration`

will give you the end date.

You can check for overlap with an existing reservation as:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Paul\_Danyliuk](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/paul_danyliuk/32/27228_2.png) [@Paul\_Danyliuk](https://connect.superhuman.com/u/Paul_Danyliuk)\
**Post date:** [November 23, 2021, 5:13am UTC](https://connect.superhuman.com/t/date-overlap-formula/27040/3 "2021-11-23T05:13:14Z")

</div>

Posted on the problem some time ago:

> [@Checking for date overlaps — the most optimal formula](https://connect.superhuman.com/t/checking-for-date-overlaps-the-most-optimal-formula/25879):
>
> TL;DR: Room.Bookings.Filter( CurrentValue.End \> Search.Start AND CurrentValue.Start \< Search.End ).IsBlank() A popular requirement is to test whether a certain date range overlaps with any of the ranges in a dataset. For example: search which of the rooms are available for requested check-in/check-out dates given the database of all bookings. There’s been a few ([1](https://connect.superhuman.com/t/optimizing-date-overlap-comparison/19124), [2](https://connect.superhuman.com/t/need-an-easy-way-to-test-for-overlapping-date-ranges-and-daterange-data-type/13809), [3](https://connect.superhuman.com/t/how-to-check-for-overlapping-events/5491)) topics about this already. Actually I even replied to one of these: My reply got lost because recently I learned th…

@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.

---

<div class="post-metadata">

**Author:** ![louca.developer](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/louca.developer/32/22685_2.png) [@louca.developer](https://connect.superhuman.com/u/louca.developer)\
**Post date:** [November 23, 2021, 7:20pm UTC](https://connect.superhuman.com/t/date-overlap-formula/27040/4 "2021-11-23T19:20:10Z")

</div>

> 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…

---

<div class="post-metadata">

**Author:** ![Pch](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/pch/32/17688_2.png) [@Pch](https://connect.superhuman.com/u/Pch)\
**Post date:** [November 23, 2021, 7:29pm UTC](https://connect.superhuman.com/t/date-overlap-formula/27040/5 "2021-11-23T19:29:14Z")

</div>

This page might help you @louca.developer 😊

> **[Optimizing slow formulas in your doc](https://help.coda.io/en/articles/3082759-optimizing-slow-formulas-in-your-doc)**
>
> Find slow formulas and increase their performance

You’ll find this 👇 somewhere in there 😊

 ![CountIf, etc...](https://global.discourse-cdn.com/coda1/original/3X/7/2/72d07f1a540d4471fb168eb6c008436db51b21a4.png)

---

<div class="post-metadata">

**Author:** ![system](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/system/32/24484_2.png) [@system](https://connect.superhuman.com/u/system)\
**Post date:** [February 21, 2022, 7:29pm UTC](https://connect.superhuman.com/t/date-overlap-formula/27040/6 "2022-02-21T19:29:25Z")

</div>

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