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
- format the fields into a human-friendly form – that’s what
DATETIME_FORMAT() does.
- Change your lookup field to point at the formatted date, not the ‘raw’ dated you are currently using.
- 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.
