How to get a blank result for Age when no DOB present?

[WilliamPorter]

I know how to test for the presence or absence of a value in another field, e.g.

IF( MiddleName != BLANK()....

But it does not seem to work with a field that should return a number result, when the parameter being fed to the formula is empty. Here’s what I think is the standard Airtable formula for an Age calculation:

DATETIME_DIFF(TODAY(),DOB,'years')

Works great so long as there is a date in the DOB field. If DOB is empty, on the other hand, this formula returns “NaN” (“Not a Number”). That’s ugly. So I tried

IF(DOB!=BLANK(),DATETIME_DIFF(TODAY(),DOB,'years'),BLANK())

But get the same result. Apparently BLANK() isn’t a number value. This wouldn’t be a problem in FileMaker, but if it were, I could coerce the (empty) result to a (non) value of type Number. In FileMaker these are both valid expressions:

GetAsDate("")
GetAsNumber("")

But Airtable doesn’t have a way to do this does it? Something like

COERCE(BLANK(),Number)

Is there another way to get rid of the “NaN” result in my Age fields? (BTW this seems related to the issues with BLANK() discusssed in this long thread:

Blank() & Zero problem - Ask the community / Formulas - Airtable Community Forum

[WilliamPorter]

Have come up with a workaround:

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

It works — gets rid of “NaN” — but I have to say that this strikes as very odd. I kind of get that Airtable doesn’t regard BLANK() as a number value. But why does it accept (an empty) date value as a valid result?

And unfortunately THIS leaves the Age field empty if the age < 1. So I need to tweak that a little more. But at least I’ve gotten rid of the “NaN” result.

I’ve gotten pretty used to it now but BLANK() still seems to me a not-totally satisfactory function…

[kuovonne · Accepted solution]

WilliamPorterTech:

BLANK() still seems to me a not-totally satisfactory function

I hardly ever use BLANK(). I am more likely to do something like

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

WilliamPorterTech:

And unfortunately THIS leaves the Age field empty if the age < 1

What do you want for people under a year of age?

[WilliamPorter]

For the record, here’s the final formula: [see my last posts]

IF(
  DOB=BLANK(),
  DOB,
  IF(
	DATETIME_DIFF(TODAY(),DOB,'years')>0,
	DATETIME_DIFF(TODAY(),DOB,'years'),
  "0"
  )
)

Works as I want. If DOB is empty, the Age field is empty. If Age is greater than 0, I see the Age. Otherwise, I see 0.

This one’s odd, too. In order to get it to work, I had to put quotes around the “0”.

[kuovonne]

WilliamPorterTech:

This one’s odd, too. In order to get it to work, I had to put quotes around the “0”.

The issue was probably something else. You shouldn’t have to put quotes around the 0. In fact, this forces your result to be a text string instead of a number, even when the age is over 0.

[WilliamPorter]

kuovonne:

What do you want for people under a year of age?

Thanks for response, Kuovonne, and for reminding me that I can just use

IF({DOB}...

To answer your question, I guess I have to say that, in the base I’m working on now, that’s a hard one. As you can see in my last post (right after yours) I decided to go with “0”. As a practical matter in this base, for this client, the age of a child who is not yet 1 is not worth greater precision. In another base, I suppose I could either create a text calc that returns something like “6 months” or a number result that returns “0.5”. But for right now, getting zero is good enough.

THANKS, Kuovonne.

[WilliamPorter]

kuovonne:

The issue was probably something else. You shouldn’t have to put quotes around the 0. In fact, this forces your result to be a text string instead of a number, even when the age is over 0.

Well I agree that I shouldn’t have to put quotes around the zero. But I do. If I remove the quotes, this is the result:

The formula that is being used to return those results is

IF(
  DOB=BLANK(),
  DOB,
  IF(
	DATETIME_DIFF(TODAY(),DOB,'years')>0,
	DATETIME_DIFF(TODAY(),DOB,'years'),
  0
  )
)

To fix that, and get a 0 into the Age field for that child born on 4/3/22, I have to put quotes around the “0”.

P.S. And by the way, once I add the quotes around “0” and get the 0 result I want, the Age column can be sorted on properly: blank fields first, 0 next, then 1, etc. There is something a little goofy about how Airtable understands certain data types.

[WilliamPorter]

And final comment. Implemented Kuovonne’s suggestion about avoiding BLANK() I edited formula to read like this now:

IF(
  DOB,
  IF(
	DATETIME_DIFF(TODAY(),DOB,'years')>0,
	DATETIME_DIFF(TODAY(),DOB,'years'),
  "0",
  DOB
  )
)

And this works, too. But with both of my recent calculations (my original one and the one following Kuovonne’s suggestion), I notice something interesting: the column values are now left aligned rather than right aligned the way number values usually are. That suggests Airtable thinks these are text values.

But they still sort as if they were numbers, not just numerals. In many other platforms I’ve worked with, if you have a bunch of numerals in a Text field, they sort as if they were letters, so you get results like this:

(blank)
1
10
2
2
20
21
27
3

But Airtable is correctly sorting these values like this:

(blank)
1
2
2
3
10
20
21
27

Odd. I may go back to the basic formula for age and live with the “NaN” results. At least it will alert my users to the absence of a DOB value, which is supposed to be required. I could fix that if Airtable supported field-level validation, but that’s another topic for another day. :slightly_smiling_face:

[WilliamPorter]

All right, I’ve wrestled this one to the ground. And I’ve marked Kuovonne’s suggestion as the solution.

The final formula I’ve ended up with is the one that she suggested = the one I should have started with:

IF(
  DOB,
  DATETIME_DIFF(TODAY(),DOB,'years')
)

Does everything I want: Returns blank if DOB is blank; otherwise returns an integer value for age, including 0. And it’s a proper number result (now aligning on the right again).

How did I go so wrong? I think I got off on the wrong foot using BLANK(). That led me into the problem with the “0” result. And I kept looking at THAT part of the problem.

Kuovonne says she hardly ever uses BLANK(). Good advice and I intend to amend my life and try to follow that advice in future.

Thanks again, Kuovonne.

William