Problem with date format & filtering

Consider this table showing today’s time logs, and the green button creating a new time log:

And another button to add a time log directly from a related entity (“Date” field is equal to Now() formula, sorry I can’t add more pictures as a new user)

This last blue “Add Time Log” button creates a date which can be used on the time log table to filter by date.

HOWEVER, the first green button “Create Time log & Start time” creates a date which CANNOT be used to filter by date on a table. That is, when the “Date” field is configured to be filled with the value from Now(). The fix is to use the formula ToDate(Now()) instead.

This doesn’t make sense to me, and definitely feels like a bug. If it is a feature and not a bug (or a special product approach), please don’t hesitate to help me understand it :slight_smile:

Hi @Gabriel_Beaudoin1

I think I see the issue you’re facing. The intricacy comes down to how Coda stores date/times and then filters on them.

DATES are stored as an integer, and TIMES are stored as a fraction of a full day (midday is 0.5). Setting your DATE column to a date/time internally sets the value of that column to a mix of an integer and a fraction. You can see this if you ToNumber() your Date column. See screenshot below, where 45393 is the “date” component, and the .772670… is the “time”


When you set your [Date] filter to a specific date, it’s looking for exact matches of that specific integer. So setting it to Today looks for all rows wherer that date translates to 45393.

Because 45393 doesn’t equal 45393.772670…, your filter finds no matching rows.

If your [Date] filter set to a range (ie, Today-Tomorrow) then your rows from today would display, because 45393.772670… is between 45393 and 45394.

The fix, as you’ve already figured out, is to make sure DATE columns are set to DATES and not DATE/TIME values. I can see how it’s confusing because the column displays as a date even when you’ve set it to a date/time.

Hope that makes sense!


Yup, that makes sense, thanks Jono!

I do feel like, when using a field of a certain type, one is expecting the value being added to this field to be adapted to the field type.

I might be missing corner cases and would love anyone that sees those to flag them. Here would be my suggestions otherwise:

  • have a field of type “date” which only stores integers
  • have a field of type “date/time” which only stores floats

Adding something to a “date” field should transform the data to an int, and adding to a date/time to a float.

Totally agree. Several ways to solve this particular case:

  • Automatically truncate the date/time value to a date value as it enters the cell.
  • Display the full (invalid) date/time value in the column and have one of those red corner triangles to let the user know the contents of the cell aren’t compatible with the column type.
  • Display the value as a date so it looks correct, but secretly hide the fact the value is actually a date/time, thus silently breaking features like the table’s filter bar. :rofl:

Doubtful they intentionally choose the third, and worst solution. So I’m standing with you that this is probably a bug.

1 Like