Filtering a Table Based on Date Controls

1

I have a reservation table with each row indicating the assignee reserved, the start date/time, and end date/time of the reservation.

Controls:

  1. Assignee (Multiple Select Setting)
  2. Start Date/Time (Date and time selection)
  3. End Date/Time (Date and time selection)

I want to use 3 different controls to filter the reservation table to a view that only reservations that have conflict with the selected start date/time and end date/time for the selected assignee.

For example: If I have the controls set to the below, I want to view all reservations of Bob that cross over between Mar 5, 2020 9 AM to Mar 10, 2020 3 PM.

  1. Assignee: Bob
  2. Start Date/Time: Mar 5, 2020 9 AM
  3. End Date/Time: Mar 10, 2020 3 PM

Let’s say I only have the following 5 reservations:

  1. Assignee: Tom, Start Date/Time: Mar 5, 2020 10 AM, Start Date/Time: Mar 8, 2020 11 AM -> This reservation will not appear in the filter because it’s a different assignee.
  2. Assignee: Bob, Start Date/Time: Mar 4, 2020 10 AM, Start Date/Time: Mar 6, 2020 11 AM -> This reservation will appear in the filter
  3. Assignee: Bob, Start Date/Time: Mar 9, 2020 10 AM, Start Date/Time: Mar 9, 2020 11 AM -> This reservation will appear in the filter
  4. Assignee: Bob, Start Date/Time: Mar 9, 2020 10 AM, Start Date/Time: Mar 11, 2020 11 AM -> This reservation will appear in the filter
  5. Assignee: Bob, Start Date/Time: Mar 4, 2020 10 AM, Start Date/Time: Mar 11, 2020 11 AM -> This reservation will appear in the filter

I’ve tried combining 2 filters to achieve the above:

  1. Set assignee column to match what’s in the interactive assignee filter
    AND
  2. Formula to filter dates that conflict: (((Start Date/Time >= Start Date/Time Control) AND ( Start Date/Time <= End Date/Time Control)) OR (( End Date/Time >= Start Date/Time Control) AND ( End Date/Time <= End Date/Time Control)) OR (( Start Date/Time < Start Date/Time Control AND ( End Date/Time > End Date/Time Control)))

Not sure why the above is not working, and open to suggestions. Also finding that the filter formula gets automatically reformatted by Coda.

2

Hi @Tina_Wu,

I was thinking about this and wondering if the date range picker would work for you or is the time important?

3

Hey Mallika, Time is important as well.

4

Are less than, greater than controls not functional when comparing dates?

5

Hi @Cassidy_Vela and Welcome to the Community :partying_face: !

I’m sorry but what do you mean by …

Could you tell us more about your setup/use case … issue (maybe) ? :innocent:

I mean, as far as I know, yes you can compare a date within a date control to another date (in a control/table/canvas formula …) using <, >, =,!=,<= and >= …
(Dates, times, Date/times are just numbers behind the scene :blush: )

E.g.: here I’m filtering a table comparing the date/time in my field Start to the 2 date canvas controls …

Date Controls
Date Controls2256×1198 267 KB

But I feel like there could be something more to your question :blush:

6

It looks like our formula had something else wrong with it and the >= is working now. Thanks!

7

A bit late, but I’m glad to know you’ve found a solution to the issue you had :blush: !