You can use the IF function:

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

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

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.

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!

@W_Vann_Hall and @Elias_Gomez_Sainz

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

Very helpful, Elias. Thanks!

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.

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.

IF(

   {Open Close},

   << YOUR ORIGINAL FORMULA HERE >>

)

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.

This is a great tip, although I’m still not quite able to do what I want.

WORKDAY({Icon Start}, 3) is my formula for adding 3 work days to the date in Icon Start. This returns an error if the Icon Start date is null, which it often is.

So I wrapped it in an IF as mentioned above:

IF({Icon Start},WORKDAY({Icon Start}, 3),0)

But now, the result of WORKDAY({Icon Start}, 3) is no longer a date that can be formatted, but rather a string, showing: May 20, 2019 2019-05-23T00:00:00.000Z

Is there a way to hide the #ERROR but also return a formatted date in the cell?

Thanks!

Hi

All you need to do is to remove the ‘, 0’ from the IF - with 0 as one of the possible returned values, Airtable cannot interpret the result of the formula as definitely a date and so displays is as a text field. You will also find you will be able to use the format option on the result.

Hope this helps,

Julian

Brilliant, thanks! That worked great.

I don’t normally write code of any kind but can sometimes manage a cut-n-paste approach. Thanks to this thread, and with experimenting, I managed to create this formula for my time sheet (after months of putting in fake times). Thanks for the help!

IF({Start},DATETIME_DIFF(End, Start))+IF({Start 2},DATETIME_DIFF({End 2},{Start 2}))+IF({End 3},DATETIME_DIFF({End 3},{Start 3}))

The time sheet allows for coming and going up to three times during the day.