Get year from dates with Select List

I have a table where I have a column using the Date column type, and have set the date options to show Month and Year (i.e. Feb 2023).

I’m trying to set up a filter with a select list, so I can filter every entry within a given year. I have had no luck so far. Does anyone have any pointers?

Hi @Stian_Ovesen :blush: !

There are multiple ways to get to that result, depending on your use case :blush:

The very first step would be to add a column to your table to extract the year from your dates with something like :
(In the sample below, this field is simply called Year :blush: )


Then, you can create a single/multi select canvas control and in the section Selectable items use something like :


This returns the list of unique years in my table named Table (:sweat_smile:)

After that, in the OptionsFilter of your table, you can add a filter such as this one :

thisRow.Year.Matches([select year])

[select year] being the canvas multi select in the sample below, this formula filters my table by the value(s) selected in the canvas control :blush: (:information_source: : Matches())

Or, you can use a table to store the years you need and a lookup/relation field to link the years to your dates :blush:

The formula within the Years lookup/relation field to pin point the appropriate year in the Years table would be something like :

Years.Filter(Year = thisRow.Date.Year()).First()

Then, you can filter your table using a relation/lookup canvas control (which is also a single/multi select control but dedicated to lookup/relation type of fields).

Coda can create it for you if you either right click on the Years field in your table, then go in FilterCanvas controlCreate new or …

In the Options of your table, once you’re in Filter, you can click on Add filter and select the appropriate field

Then, instead of Is any, you should select : Uses canvas control.

All that’s left is to click on Create control

I hope this helps :innocent:


Not sure what you mean with a select list?

But you can create a column with the formula datecolumn.year(). That will extract the year from the date, and you can then filter on the column, with any of the Coda filter tools.

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