Best practice to filter by date

1

perhaps someone here knows of a cleaner/better/more elegant way to do this

i have a simple table with Events and there is a column for Start date/time

i have a control where the user can select a date and i want to filter the events only for that date

with this formula Events.filter(Start = date_filter_control) i get no matches. presumably because the comparison function is also looking for a match of the time, but the date_filter_control does not have a time, only a date.

i made it work like this Events.filter(Start > date_filter_control AND Start < date_filter_control + 1) in other words, the start date is at least one second past midnight of the date on the date_filter_control and less than the beginning of the next day.

the reason for this post is that i would think i could make this work by comparing the start column to the date_filter_control, but that comes back blank. however, this approach does seem to work in other places, maybe someone can explain what i am not getting about how to compare dates

this is a calculated column in a table that summarizes the Events table:

Events.Filter(thisRow.Name = Category AND Start = Today()).Duration.Sum()

in the formula above, i am comparing the same Start column, this time to Today() and here it works, even though today is only a date (no time) like in the date_filter_control.

i suspect i may not be getting something really basic or maybe this is just how it works

2

Filtering for a specific date/time when you’re filtering by just a date can be fixed by using the Matches() formula.

Instead of [Start] = [Date_filter_control],
try doing something like [start].matches([date_filter_control]).

From my understanding, Matches() just checks to see if anything is matching, hence why it’ll work because the base date in [Start] matches the control’s date even though it also has time.

3

got it. thank you. :+1: that works. i knew there had to be a better way. i did not want to use such awkward formulas. i was sure coda had something better in mind for this

4

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