Formula resulting in NAN

[Celina_Rollon]

Hey all, I’ve been trying to work this out on my own but it’s providing super difficult. I have a birthdate field and I want to find out the age. I am using DATETIME_DIFF(NOW(), Birthday, ‘years’) & ’ years ’ and I see the age however those that haven’t filled in the birthday field say Nan. I would love some help. Thank you

[Jeremy_Oglesby]

Hey, @Celina_Rollon,

You are getting the NaN error because the DATETIME_DIFF() function attempts to do subtraction of the date in the {Birthday} field from the date NOW(). Those dates are converted into number values before doing the subtraction, since subtraction can technically only be performed with number values. However, when the {Birthday} field is blank, it is returning null(or “empty”) to the formula, and the formula is trying to convert null into a number and can’t. So it’s returning NaN, or “Not a Number” to you to let you know what’s going wrong.

You can fix this by adding a conditional statement telling the formula to only execute if there is a value in the {Birthday} field, like this:

IF(
   {Birthday},
   DATETIME_DIFF(NOW(), {Birthday}, 'years') & ' years'
)

[Celina_Rollon]

YAY thank you so much. That worked.

And if you don’t mind (and if its possible)

If someone filled out their DOB and put 2020 by accident is there a way I can not show the age for any birthdays in the year 2020 alongside the formula you gave me to get rid of the Nan? In the case of this in the age column it would be blank instead of saying 0 Years?

[Jeremy_Oglesby · Accepted solution]

IF(
   AND(
      {Birthday},
      NOT(DATETIME_DIFF(NOW(), {Birthday}, 'years') < 1)
   ),
   DATETIME_DIFF(NOW(), {Birthday}, 'years') & ' years'
)

[Celina_Rollon]

Wow!!! You are amazing. THANK YOU THANK YOU!! I wish my brain could work it out. Is there a good video tutorial or anywhere I can learn formulas better? Or do I need to be a mathematician?

[Jeremy_Oglesby]

The community leader @Justin_Barrett has a youtube channel where, in at least some of his videos, he addresses using formulas. You might check that out.

I think it’s less to do with mathematics, and more to do with programming. My experience with developing web applications, working with SQL, and also working with Excel formulas has been helpful in understanding how to piece together formulas that do what I need them to do in Airtable.

[Celina_Rollon]

Thank you so much for your insight. Good to know. Do you ever do consultancy work? Ie if someone paid you to come in and help develop an airtable? I can’t afford it just yet but I might want to do this in the future so would be good to keep your info if you do!

[Jeremy_Oglesby]

Yes, I do, from time to time. I have a full-time job and 3 children, so I try not to take on too much, but I do enjoy doing smaller-scale (as in, not enterprise-scale) Airtable solutions. Feel free to ping me if you are interested in that.