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

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…

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

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”.

IF(
  DOB=BLANK(),
  DOB,
  IF(
	DATETIME_DIFF(TODAY(),DOB,'years')>0,
	DATETIME_DIFF(TODAY(),DOB,'years'),
  "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.

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

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.

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.

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

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:

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

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