Skip to main content
Solved

Keep only latest date in a lookup field

S
Forum|alt.badge.img+5
  • SBeau
  • New Participant
  • 4 replies

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?

Best answer by ScottWorld

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

View original
Did this topic help you find an answer to your question?

4 replies

ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8779 replies
  • Answer
  • December 19, 2024

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

DisraeliGears01
S
JeffreyW
4 people like this
  • DisraeliGears01
    DisraeliGears01
  • S
    SBeau
  • JeffreyW
    JeffreyW
  • MJen
    MJen
S
Forum|alt.badge.img+5
  • SBeau
  • Author
  • New Participant
  • 4 replies
  • December 19, 2024

Thank you @ScottWorld! This works perfectly! ðŸ’¡

ScottWorld
1 person likes this
  • ScottWorld
    ScottWorld
DisraeliGears01
Forum|alt.badge.img+17

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.

S
1 person likes this
  • S
    SBeau
S
Forum|alt.badge.img+5
  • SBeau
  • Author
  • New Participant
  • 4 replies
  • December 19, 2024

@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!

Reply


Most helpful members this week

Terms & Conditions