Skip to main content

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

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


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

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.

Reply


Terms & Conditions