Skip to main content
Solved

Formula to calculate age in years & months

  • May 8, 2020
  • 2 replies
  • 56 views
R
Forum|alt.badge.img+3

Hi all just trying to decide if AirTable will work for our businesses. So far so good except for this snag. We are in childcare and NEED to calculate our babies ages in Months not years based on TODAY().
This is the section of the child table so far, I want the Age field to return ages under 3yrs (<=35months) in months and the rest in years…

Formulas so far
M_age DATETIME_DIFF(TODAY(),{C1_DOB},‘M’) &“M” (works fine)
Y_age DATETIME_DIFF(TODAY(),{C1_DOB},‘Y’) &“Y” (works fine)
Age I’ve tried… to get under 3 years in Months and over in years:
IF(M_age <= 35,(DATETIME_DIFF(TODAY(),{C1_DOB},‘M’)&“M”),Y_age)
IF(M_age <= 35,M_age,Y_age)

No matter what I do the age field returns age in years.

Best answer by Mohamed_Swella1

Hi @RJ_Southam,

Welcome to Airtable Community ! :grinning_face_with_big_eyes:

I ll assume you will still have the other 2 fields in my answer (Y_age and M_age).

The problem with you formula you are using for Age is that M_age and Y_age are not numbers, they are text. So you cannot use them this way. i.e.: the IF formula will always return false because M_age is never less than 35 (since it is a text).

In this case, the formula you are looking for would be

IF(DATETIME_DIFF(TODAY(),{C1_DOB},‘M’) <= 35, M_age, Y_age)

If you want to remove the M_age and Y_age fields, you can also do that with a longer formula. Simply, add the formulas in the M_age and Y_age in the IF formula above.

BR,
Mo

2 replies

Mohamed_Swella1
Forum|alt.badge.img+17

Hi @RJ_Southam,

Welcome to Airtable Community ! :grinning_face_with_big_eyes:

I ll assume you will still have the other 2 fields in my answer (Y_age and M_age).

The problem with you formula you are using for Age is that M_age and Y_age are not numbers, they are text. So you cannot use them this way. i.e.: the IF formula will always return false because M_age is never less than 35 (since it is a text).

In this case, the formula you are looking for would be

IF(DATETIME_DIFF(TODAY(),{C1_DOB},‘M’) <= 35, M_age, Y_age)

If you want to remove the M_age and Y_age fields, you can also do that with a longer formula. Simply, add the formulas in the M_age and Y_age in the IF formula above.

BR,
Mo

R
Justin_Barrett
R
Forum|alt.badge.img+3
  • RJ_Southam
  • Author
  • Participating Frequently
  • May 10, 2020

Thanks so much, of course it is was the text vs integer, I actually just removed the & “m” and the formula worked so M_age and Y_age are both integers and easier to work with. This is what I ended up with.

(added the round function to eliminate decimals)
M_age ROUND(DATETIME_DIFF(TODAY(),{C1_DOB},‘M’),0)
Y_age DATETIME_DIFF(TODAY(),{C1_DOB},‘y’)ROUND(M_age-(Y_age*12),0)
Age IF(M_age <= 35,(DATETIME_DIFF(TODAY( {C1_DOB},‘M’)&“M”),CONCATENATE(Y_age&"Y ",Mth&“m”))

Mohamed_Swella1
Terms & ConditionsAccessibility statement