Help

Function to test if date field is empty or null

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
2236 3
cancel
Showing results for 
Search instead for 
Did you mean: 
DanBliss
5 - Automation Enthusiast
5 - Automation Enthusiast

I created a table called Items (household items).  In Items, there is a partially filled-out field, PurchaseDate, that is some items have a PurchaseDate; other items don't have a Purchase Date (field is left blank).  I want to run a filter of those items with a blank PurchaseDate:  Is the best way to do this is to create a formula field, named noPurchDate: LEN(DATESTR({PurchaseDate))=0 such that if the PurchaseDate is not empty, noPurchDate returns FALSE, and if PurchaseDate is empty, then NoPurchDate returns TRUE? Is there an easier way to get this result? 

Another more "normalized" way of handling this might be to create a separate table, PurchasesWithDates linking Items to PurchasesWithDates.  How does one create a filter of Items with no link to PurchasesWithDates?

 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

This is okay too

IF({PurchaseDate}, "hasDate", "hasNoDate")

Also, have you tried filtering date fields with "is empty"?

See Solution in Thread

3 Replies 3
DanBliss
5 - Automation Enthusiast
5 - Automation Enthusiast

DATESTR() seems not to be able to handle empty sets nicely, but BLANK() does, so instead of LEN(DATESTR()), here's what I found does work:

IF({PurchaseDate} = BLANK(), "hasNoDate", "hasDate"). 

If the name of the field name is hasPurchaseDate, then IF({PurchaseDate} = BLANK(), FALSE(), TRUE()) works OK;  It would be even better if AirTable had a Logical primitive value or the resulting format would be T/F or perhaps a checkbox. 

Sho
11 - Venus
11 - Venus

This is okay too

IF({PurchaseDate}, "hasDate", "hasNoDate")

Also, have you tried filtering date fields with "is empty"?

DanBliss
5 - Automation Enthusiast
5 - Automation Enthusiast

Sho, 

Filtering date fields with "is empty" does do the trick.  Forgot to croll al the way down to find that option!  (How embarrassing!)  Thanks for your tips.