Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Formula to calculate age in years & months

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
2246
2

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 07, 2020 10:56 PM

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.

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 09, 2020 10:51 AM

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

Reply

2 Replies 2

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 09, 2020 10:51 AM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 09, 2020 09:59 PM

(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