Lookup fields lose data type

If I have a lookup field for a date, it loses its data type as a “date” and I cannot create a timeline block or place events on a calendar. The only solution seems to be making a new column with the data type of “date” and copy/pasting the lookup field info over… manually, every time there is a change or new entry.

Lookup fields return arrays — even if there’s only a single element to the array. Usually Airtable manages to suss out the field type and recognizes it as a date… but not always.¹ Instead of a lookup field, try defining it as a rollup field, one that follows the same link and references the same field in the linked record, but use the following aggregation function:

DATETIME_PARSE(values&'')

That should work — but if it doesn’t, you may need to add a format specifier. First, determine in what format the referenced date is being passed by using the aggregation function

values&''

Then, modify the first ag function to provide an explicit format specifier, using the codes given here. For example, you might use

DATETIME_PARSE(
    values&'',
    'MM-DD-YYYY'
    )

  1. Hmmm… it just occurred to me this may be a function of whether the linked-record field is set to allow links to multiple records; I’ll have to test that hypothesis some time
1 Like

That had not occurred to me, but I just made sure that the linked fields were set to only allow one record. No luck.

Yeah, I’m still not sure why Airtable can sometimes identify a looked-up date and sometimes not. AFAIK, though, DATETIME_PARSE(values&'') always works — although, conceivably, it might hiccup on certain browser/OS locale settings. If so, DATETIME_PARSE(values&'',[FormatSpecifier]) should take care of it.