Skip to main content

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.

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

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

Reply


Terms & Conditions