- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2024 01:33 PM - edited ‎Dec 19, 2024 02:16 PM
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?
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2024 03:11 PM
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2024 03:11 PM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2024 03:34 PM
Thank you @ScottWorld! This works perfectly! 💡
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2024 03:48 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 19, 2024 03:59 PM
@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!
