Using Find() with Lookup Field

Hi there!

The problem is likely because the lookup field is returning a formatted array of dates. If you cast your lookup field to a string (or change it into a rollup field), you’ll see that the dates are actually formatted something like… “2019-03-21T17:23:00.000Z”.

There may be a better workaround, but here’s one that I found… Add a field to the table with the dates that you want to lookup that formats each date the way you want, but doesn’t parse it into an actual date. I’d use a formula like…

IF(Date, DATETIME_FORMAT(Date, 'M/D/YYYY'))

Then change your lookup field, {Dates Used}, to point at this new field. Hope that helps! :slightly_smiling_face:

I’m not sure I follow.

The Date Lookup field is pulling the date from a Date Field on another table. Maybe I should have mentioned that?

EDIT:
Well using your post as inspiration. I was messing with the rollup field and used it on the Date Field that is being looked up and used CONCATENATE(values). That showed me how the dates were being formatted. Plugged that into my original formula and I’m much closer. Some dates are still giving me the wrong result.

Internally, Airtable stores dates in a standard format that doesn’t match any commonly used, human-readable format. (Unless that human is a Unix programmer, that is…) When you define a datetime field and specify formatting — or when you define a datetime field and and specify formatting — Airtable shows you that date in a human-friendly format according to your preferences, either explicit or default.

A lookup field, though, is treated by Airtable as an array of whatever type the looked-up field is — so a lookup of date fields is returned, internally, as an array of Unix-time fields. Airtable, still eager to be friendly, shows that to you as a string of comma-separated human-readable dates — but when it uses that lookup field in a formula, it sees them differently.

What @AlliAlosa was saying is that instead of performing your FIND() against an array of Unix-formatted datetimes, you need to

1. format the fields into a human-friendly form – that’s what DATETIME_FORMAT() does.
2. Change your lookup field to point at the formatted date, not the ‘raw’ dated you are currently using.
3. Perform your FIND() against that.

(Actually, instead of using a lookup field in Step 2, I’d recommend using a rollup field. Define it the same as the lookup field — but roll up the formatted date — and use an aggregation function of ARRAYJOIN(values). That will make {Dates Used} into a comma-separated string. In your case, FIND() will probably work correctly against either a lookup or rollup of formatted dates — but in some applications a lookup might give erroneous results.)

That’s probably 175% more explanation than you needed, but the bottom line is that @AlliAlosa’s solution is the correct one, and it’s because of the difference in how Airtable sees an array of dates and how it displays it to you.