Keep only latest date in a lookup field

[SBeau]

I am looking for a way to parse a lookup field (Sprint Start Date) that contains multiple, comma-separated date values, and pull the most recent date into a new field. 

For example:

• IF --> Sprint Start Date = 2024-10-12, 2024-12-22
• THEN --> Formula Field = 2024-12-22 

I have tried to use the following formula to parse the Sprint Start Date field and convert it, but it always returns the same value, or an error:

DATETIME_PARSE(MAX(IF({Sprint Start Date}), VALUE(DATETIME_FORMAT({Sprint Start Date}, 'YYYYMMDD'))))

Outcome #1: 12/31/1969 
Outcome #2: #ERROR!

I have also tried this, which gets me closer, but not quite what I'm after:

DATETIME_PARSE(MAX(IF({Sprint Start Date}), VALUE(DATETIME_FORMAT({Sprint Start Date}, 'YYYYMMDD'))),'YYYYMMDD')

In this case, I get one of two outcomes:

Outcome #1: The correct date, minus one day

• e.g. If Sprint Start Date = 12-02-2025 the formula field returns 12/01/2025
• This happens when there is only one value in the date field

Outcome #2: #ERROR!

• This happens when there is more than one date in the Sprint Start Date field

I feel like I'm close, but could use some help getting this right. Can anyone help with this?

[ScottWorld · Accepted solution]

You actually don't need the lookup field at all. Instead of the lookup field, you will need to create a rollup field that points to your date field in the other table.

Then, you will use this formula in your rollup field:

MAX(values)

That will give you the latest date.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[SBeau]

Thank you @ScottWorld! This works perfectly! 💡

[DisraeliGears01]

Scott's right on, you need a Rollup Max(values) field. Just chiming in on a small component...

You mentioned you're getting the correct date minus 1 day, I'd bet that's a timezone issue, as Airtable loves to put things in UTC/GMT. I was having the same problem recently on exactly this rollup, and realized that even with time turned off on my date, the system was assigning 12AM GMT but then changing it to local (PST) during the rollup, pushing me back a day. In your rollup, make sure to go to Formatting and select use the same time zone for all collaborators, that should fix your -1 day issue.

[SBeau]

@DisraeliGears01 funny enough, when I started using the rollup field type, the -1 day issue went away. 🤔

Will keep this in mind if I see it happening again though. Thanks for the help!