Calculate age groups from a formula

Viktor_Svensson · ‎Jan 21, 2021

Hi, i need help with a formula if this is possible.

i have a table containing a “date” field and a “formula” field that calculates the age from that.
( DATETIME_DIFF(TODAY(), {Person nr}, ‘years’) & ’ years, ’ & MOD(DATETIME_DIFF(TODAY(), {Person nr}, ‘months’), 12) & ’ months’ )

everything is perfect here so far, but i would like to make a new field that contains age groups,
like 0-10, 10-18, 18-30, 30-50, 50-100 lets say. is there a formula for making this? and can it change buy it self when a person goes from let say age 29 to 30 and so on?

or

is there a way to search, filter or group already for lets say ages between 20-40 or 30 and up? or 25-45?

Hope you understand what i am looking for, and every help i can get would be much appreciated!

Thanks,
Viktor

Update:
if its more simple to remove the “months” thats ok.

kuovonne · ‎Jan 25, 2021

You’re on the right track but it needs some adjusting. In Airtable this is done with nested IF statements.

IF( {age} < 13, "0-12",
IF( {age} < 19, "13-18",
IF( {age} < 30, "19-29",
IF( {age} < 46, "30-45",
IF( {age} < 61, "46-60",
IF( {age} < 101, "60-100", "101+"
))))))

Note that this will only work if there is an age. If there isn’t an age, the formula will group the record in the “0-12” group. Also note that my first age group starts at 0 instead of 1. There are ways of adjusting the formula to take care of these issues if they are important, but this should be enough to get you started.

See Solution in Thread

Andrew_Wright · ‎Jan 21, 2021

@Viktor_Svensson What is the Output of the Age Calculator you already have?

Viktor_Svensson · ‎Jan 21, 2021

sorry if im not understanding correctly, but the output is for example

36 years, 8 months

Viktor_Svensson · ‎Jan 21, 2021

and what i would like is the age ( 36 years, 8 months ) to be automatically become part of the age group

Viktor_Svensson · ‎Jan 21, 2021

maybe this makes more sense?

Andrew_Wright · ‎Jan 21, 2021

I worked this out in my test base. So here is my suggestion.
The Formula that is returning the Age in Years and Months needs to be just a single number. If you want to keep that field as is for readable purposes that’s find but you are going to need another field that outputs the age as a single number I recommend in days.

Then you can use an Airtable automation. When a Record Matches Condition - WHEN {Age Formatted in Days} > (The number of days in the min of the age group). AND {Age Formatted in Days} < (The number of days in the max of the age group).

@Viktor_Svensson You will need an automation for each age group. So 7 automations in your case.

The action would be Update Record - The field would be your single select with the option typed in as exactly how it is in the single select.

Does that make sense? This may not be the best way to do it but it will work

Andrew_Wright · ‎Jan 21, 2021

I am going to have to work on this more to see if it is possible with formulas instead of 7 automations.

Viktor_Svensson · ‎Jan 21, 2021

Thank you for helping me! much appreciated. if there is possible without automations that would be great, but if thats the way, then thats fine too.

and if my setup is not the best, that can change too. so if there is another way to have “age” and “age groups” that would be more then ok. i can start all over.

Viktor_Svensson · ‎Jan 25, 2021

Hi, this might be too hard to do ( the above ) so what i maybe can do is:

have a “date” field

and a “formula” field that calculates the age in one number “15” lets say

DATETIME_DIFF(TODAY(), {Date}, ‘years’)

and then have another “formula” field that groups all ages 1-100 in “1-12”, “13-18”, “19-29”, “30-45”, “46-60” and “61-100”

Formula found on internet but doesn’t work for me?

IF(
{Age} <= 12, ‘1-12’,
IF(
{Age} = 13, ‘13-18’,
IF(
{Age} = 14, ‘13-18’,
IF(
{Age} = 15, ‘13-18’
IF(
{Age} = 16, ‘13-18’
IF(
{Age} = 17, ‘13-18’
IF(
{Age} = 18, ‘13-18’
IF(
{Age} = 19, ‘19-29’
IF(
{Age} = 20, ‘19-29’
)
)
)

and so on…

then i could have a “app” with a “Chart” for: “1-12”, “13-18”, “19-29”, “30-45”, “46-60” and “61-100”
right???

Viktor_Svensson · ‎Jan 25, 2021

Again, if there is a simpler way or another way to see the “age” and a “age group” that automatically calculates the “age” and “age group”. that would be amazing.

Thanks in advanced