IF(FIND) formula not working?

Hi, I’m using the following formula in the column “is absent today”, but for some reason it isn’t correctly finding August 16th. I suspect this is because the linked “Absent” field is an array, which is why I included the &"" in my formula, but that didn’t fix it…

IF(FIND({Today's Date},{Absent} & ""), 'Y', 'N')

The {Absent} field is just a link field, and link fields actually return a string. You can learn more about field return types in the base that I built to track such things:

I suspect that the problem is with your {Today's Date} formula field. If you’re just using the TODAY() function in that formula, that outputs a datetime, which is a special data object that contains both the date and time (even though you don’t see a time in that field, it’s still stored under the hood). While the field formatting is designed to make that look readable, the field returns that datetime data object to formulas.

The problem then becomes a matter of comparing the datetime from {Today's Date} to the string from {Absent}. In such cases, Airtable will often revert to converting the datetime to a string, but it doesn’t do that based on how it’s displayed in the field. A raw datetime-to-string conversion gives you a string like this, which explains why the formula never finds a match:

2021-08-16T00:00:00.000Z

What you need to do is format the date into a string that matches what you see in {Absent}, and then find that. Try this formula:

IF(FIND(DATETIME_FORMAT({Today's Date}, "MMMM D, YYYY"), {Absent} & ""), 'Y', 'N')

You could actually combine the two fields into one formula like this:

IF(FIND(DATETIME_FORMAT(TODAY(), "MMMM D, YYYY"), {Absent} & ""), 'Y', 'N')

NOTE: the TODAY() function returns the date based on GMT. To accurately compare against your local timezone, you’ll need to shift that value before formatting it:

IF(FIND(DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "timezone_specifier"), "MMMM D, YYYY"), {Absent} & ""), 'Y', 'N')

Use this page to find what to use for timezone_specifier for your local timezone:

2 Likes

That worked perfectly, thank you!

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.