Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 28, 2020 04:53 PM
What’s wrong with this formula? It references a single field named ‘Date’. That field usually will have a valid date in it, but in some cases the field might be left empty. In a separate formula field I’m trying to use this formula but it’s not working.
IF ( Date = BLANK(), “Date Missing”, DATETIME_FORMAT(Date,‘YYYYMMDD’) )
What’s wrong with that? How else do I test to see if a field is empty?
William
Solved! Go to Solution.
Mar 28, 2020 04:57 PM
Airtable formulas are really picky on syntax.
Remove the space between IF
and the opening parenthesis.
IF( Date = BLANK(), "Date Missing", DATETIME_FORMAT(Date,'YYYYMMDD') )
Mar 28, 2020 04:57 PM
Airtable formulas are really picky on syntax.
Remove the space between IF
and the opening parenthesis.
IF( Date = BLANK(), "Date Missing", DATETIME_FORMAT(Date,'YYYYMMDD') )
Mar 28, 2020 05:24 PM
THANK YOU – that was it! I’m going to have to review the formula reference again because I missed this tip if it’s in there.
William
Mar 29, 2020 03:19 AM
You can also use a slightly more concise notation to check for an entry on any field:
IF(NOT(Date), 'Date missing', 'Date entered')
or the inverse:
IF(Date, 'Date entered', 'Date missing')
or more generally:
IF({Field Name}, ...)
JB
Mar 29, 2020 07:17 AM
Just be careful with this for numeric fields. The IF
statement will treat the number zero as false, and may not produce the result you want. For numeric fields `IF({field} = BLANK(), …) is safer.
You also need to be careful if you are chaining formula fields that return empty strings.
Mar 29, 2020 10:55 AM
Ah, thanks for that tip. And I do understand that this should be avoided with number fields (since a zero in the field will get interpretated as a false or empty).
FileMaker has an IsEmpty() function, so in FileMaker I’d write this calc
If ( IsEmpty ( DateField) ; "Date missing" ; "Yep, there's a value in the Date field!" )
I’m trying to learn how to do same thing in Airtable. I was using “If ( Date=BLANK()…” and the results weren’t reliable. I thought that I might be misusing the BLANK() funciton, but Kuovonne set me straight by commenting that there can’t be a space between the “IF” and the open parenthesis.
Thanks to you both.
William