Filtering a Table Based on Date Controls

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.

Hi @Tina_Wu,

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

Hey Mallika, Time is important as well.