Join date-elements from two columns into a single column

[Gideon_Klionsky]

I’ve knocked my head against this for a while, including seeing a bunch of possibly related posts. My twist is that I’m trying to use two different data types.

OK:

I’m keeping track of something over time. These days, I can record the date it happens. For stuff that happened a while back, I only know the year. Therefore in a certain Table, I have one column for Dates (if known) and another column for Year (if date not known) . The Dates column links to a Table where each record is a date. The Year column is a multi-select dropdown list.

What I want to do is make a column called Year Overlay that extracts the year from any date in the Dates (if known) column and joins it with the year from the Year (if date not known) column.

 CHAPTER    Date (if known)           Year (if date not known)     Year Overlay 
           (link to Dates table)     (Multi-select)             !! HELP!!`
 Ch 1       2020/10/22                2006                         2006, 2020 
 Ch 2       2020/07/04, 2015/01/01                                 2015, 2020
 Ch 3                                 2008, 2010                   2008, 2010 
 Ch 4       2018/01/01, 2014/01/01    2007, 2016                   2007, 2014, 2016, 2018 (ideally order by year)
                                                                   2007, 2014, 2016, 2018 (but realistically order by column)

[Justin_Barrett]

This is doable, though there’s no way to sort the dates because Airtable does not have any sorting options in its formula structure. If sorted dates are important, you’ll need to skip the description below and do everything in a script (either in the Scripting app, or in an automation). If you’d like help with that setup, let us know.

Here’s the end result of the formula version:

To set this up, you’ll first need to add a formula field named {Year} to your [Dates] table, using this formula (replace “Date” with the reference to your date field):

YEAR(Date)

In your main table, add a rollup field named {Year Rollup} to roll up the values from this new {Year} field based on the links in the {Dates (if known)} field. The aggregation formula should be:

ARRAYJOIN(values, ", ")

This will create a string containing comma-separated years based on the linked dates.

The formula for the {Year Overlay} field would then be this:

IF({Year Rollup}, {Year Rollup} & IF({Year (if date not known)}, ", ")) & IF({Year (if date not known)}, {Year (if date not known)})