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
- Calculate age groups from a formula

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
5219
11

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

Jan 21, 2021 03:19 AM

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.

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

Jan 25, 2021 08:04 AM

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.

Reply

11 Replies 11

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

Jan 21, 2021 06:11 AM

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

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

Jan 21, 2021 06:17 AM

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

36 years, 8 months

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

Jan 21, 2021 06:21 AM

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

Jan 21, 2021 06:24 AM

maybe this makes more sense?

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

Jan 21, 2021 06:37 AM

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

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

Jan 21, 2021 06:43 AM

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

Jan 21, 2021 06:58 AM

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.

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

Jan 25, 2021 03:52 AM

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???

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

Jan 25, 2021 04:07 AM

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