Extract Base Metadata Expansion

[David_Y]

Hello !

I’m new to airtable and struggling to write a formula that allows me to extract dates from a field. I have tried many different formulas found online but nothing matches my case.

I have a SPRINT column that hosts in each field a set of dates: 2022-01-03, 2022-01-10, 2022-01-17… (can go up to 53 dates).

I would like to extract from any of the fields in the SPRINT column the FIRST date (2022-01-03) and send it to another field in a new column named START.

Then, extract from any of the fields in the SPRINT column the LAST date (2022-01-17) and send it to another field in a new column named END.

Do you think that is possible and if so could you help me find out the correct formula ?

Thanks !

[Justin_Barrett]

Welcome to the community, @David_Y! :grinning_face_with_big_eyes: I’ve got a few ideas of how to pull this off, but before I can get to that, what type of field is your {SPRINT} field? Single line text? Lookup? Link to records in another table? Something else?

[David_Y]

Hi @Justin_Barrett ! Thanks for taking the time to check out this issue. :slightly_smiling_face:

In the TASKS table, I have a SPRINT DATES column, which is linked to the SPRINTS table, from which I pull the dates from.

Then, in the TASKS table, I’ve setup a SPRINTS column which is a Lookup field, linked to the SPRINT DATES column.

[Justin_Barrett · Accepted solution]

Sorry for the delay, @David_Y . This can be done using a couple of rollup fields in your [Tasks] table. You probably won’t even need the existing lookup field because the rollup fields will operate directly on the retrieved dates under the hood.

A rollup field has the same initial setup as a lookup field, but it adds what’s called an aggregation formula, which processes the array of retrieved values from the linked records. In your case, you could have a {Start} rollup field in the [Tasks] table that rolls up the start dates of all linked sprints, then uses the MIN(values) aggregation formula to find the earliest of those dates. A similar rollup field named {End} could pull in the end dates of all linked sprints and find the latest date using the MAX(values) aggregation formula.

[David_Y]

Hi @Justin_Barrett !

Your solution solved my problem in a simple and elegant way.
Thanks a lot for your help and your thorough explanations, I really appreciate it ! :winking_face:

Cheers,