How can I see if a field is empty or not?

[WilliamPorter]

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

[kuovonne · Accepted solution]

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') )

[WilliamPorter]

kuovonne:

IF( Date = BLANK(), “Date Missing”, DATETIME_FORMAT(Date,‘YYYYMMDD’) )

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

[JonathanBowen]

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

[kuovonne]

JonathanBowen:

or more generally:

IF({Field Name}, ...)

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.

[WilliamPorter]

JonathanBowen:

You can also use a slightly more concise notation to check for an entry on any field:

IF(NOT(Date), 'Date missing', 'Date entered')

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