How do I return nothing when a field is empty as opposed to "#ERROR"?


#1

When my Birthday field is empty, how do I get Age to show nothing as opposed to “#ERROR”? Here is my formula at this point:

DATETIME_DIFF(TODAY(), {Birthday}, ‘years’)

Thanks for any help given.


#2

You can use the IF function:

IF({Birthday}, DATETIME_DIFF(TODAY(), {Birthday}, ‘years’))


#3

Thanks, but I get an “invalid formula” response with this.


#4

I just copied it from a test base where it’s working. I suspect the problem is the quote, you should understand the recommendation and try yourself and not only copy and paste.


#5

You should be able to copy-and-paste this version of the formula successfully. (Note the difference in the single quotes: The version Elias posted had been ‘pretty formatted’ into ‘inverted comma’ style opening and closing single quotes — which Airtable does not like. Instead, you need to use the ‘straight single quote’ found in the following version. Alternatively, you can do as Elias suggests and simply overwrite the curly quotes with the straight one found on your keyboard.)

IF({Birthday}, DATETIME_DIFF(TODAY(), {Birthday}, 'years'))

.__________

@Elias_Gomez_Sainz I learn something new every day. Although I regularly use abbreviated comparisons in IF() statements when testing for zero vs. non-zero — e.g., IF(FIND('.',SomeText),This,That), for some reason it hadn’t sunk in I could do the same thing with BLANK() vs. not-BLANK(). Until about 5 minutes ago, I would have written that formula as

IF(Birthday=BLANK(),BLANK(),DATETIME_DIFF(TODAY(),Birthday,'years'))

Thanks for helping me trim redundant code!


#6

@W_Vann_Hall and @Elias_Gomez_Sainz

That worked like a charm! Thanks to both of you guys for the help.


#7

Very helpful, Elias. Thanks!


#8

W_Vann_Hall, what am I doing wrong? I have the same need to return nothing from nothing. I literally want nothing for nothing, but get the same #ERROR. Here’s my original formula based on a date:
WORKDAY(DATEADD({Open Close},{Day #}-1,‘days’),1,‘2018-09-3,2018-10-08, 2018-11-12,2018-11-22,2018-12-25,2019-01-01,2019-01-21,2019-02-18,2019-05-27,2019-07-04,2019-09-02,2019-10-14,2019-11-11,2019-11-28,2019-12-25’). But if there is no original date (ie. {Open Close} is empty), how do I craft the IF? Encapsulating all in IF (junk here) did not work. See: IF(WORKDAY(DATEADD({Open Close},{Day #}-1,‘days’),1,‘2018-09-3,2018-10-08, 2018-11-12,2018-11-22,2018-12-25,2019-01-01,2019-01-21,2019-02-18,2019-05-27,2019-07-04,2019-09-02,2019-10-14,2019-11-11,2019-11-28,2019-12-25’)).
THANKS MUCH.


#9
IF(
   {Open Close},
   << YOUR ORIGINAL FORMULA HERE >>
)

This checks for the existence of any value in {Open Close}. If it finds a value, it moves on to the next step and evaluates your formula. If it finds no value, it stops evaluating and leaves the cell blank.


#10

OMG it worked. I see now how the IF works. If ({Something exists here}), then move on to what is after the comma. At first I got “invalid formula” but then realized I had added an accidental variable where my cursor landed. Happens to me all the time. Thanks so much for the educational tidbit. This formula stuff is not straight-forward to people like me who are logical but who have not written it before.