Unable to use date-based filter logic in date column that is a formula

1

Hi all,

I want to apply a filter a table to show me only rows with a date before today or this year. The date I am trying to use is based on a formula (another date +5 years), and I’m trying to use filter logic, but Coda seems to treat these kinds of dates as text for the purposes of filtering - thus I’m not able to choose ‘before’ ‘after’, etc. Anyone know answers for this?

2

Hi @Tim_Richardson1,
Could you please share your doc (or a sample of it) so that we could dig into it?

It seems very much a matter of column data type or type conversion, but I’m sure that looking at the context would help.

Thank you!

3

Dear @Tim_Richardson1,

As @Federico_Stefanato mentioned, it’s always better to add a dummy doc to understand your use case.

I made below a quick sample that might suite you, by using controls to have full flexibility on your filters.
On top of it, I would recommend to filter only on views and to keep the main base intact.

3 Likes
4

Thanks for the responses! Here’s a link to a demo coda doc: https://coda.io/d/Untitled_dVHelVKuWLt/_suIUg

And here’s a gif showing the issue. If a date is produced by a formula it is treated as text for filtering purposes: https://dsc.cloud/Timdropshare/Screen-Recording-2019-12-13-15-37-52

5

Hi @Tim_Richardson,

There are a couple things to check here. If you change the column format type to “Date”, it should respect that format in the dropdown for filtering and other items. Right now its formatted as a Text column so it’s reading it as a text field.

Depending on how you are formulaically creating the date, you might need to add the ToDate() formula to convert it to a date data type.

For this instance, with calculating the Year, Month, and Day separately, you might need to use the ToDate() formula.

6

You’re right! I thought that once a date column was a formula and formatting as a date would break the formula mode. Didn’t realize it can be both a formula and a date…

I notice when I CONCATENATE a date that has been formatted a certain way, it is interpreted as the actual underlying date info. So if I CONCATENATE("[date]") where date row reads “August 2019” then it will show up as “2019-08-01” (which is incorrect because the day is not meaningful data).