Help

Formula to calculate age in years & months

Topic Labels: Formulas
Solved
Jump to Solution
1095 2
cancel
Showing results for 
Search instead for 
Did you mean: 
RJ_Southam
5 - Automation Enthusiast
5 - Automation Enthusiast

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…
image

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.

1 Solution

Accepted Solutions
Mohamed_Swella1
13 - Mars
13 - Mars

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

See Solution in Thread

2 Replies 2
Mohamed_Swella1
13 - Mars
13 - Mars

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

RJ_Southam
5 - Automation Enthusiast
5 - Automation Enthusiast

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.
image
(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”))