Formula to separate adults and minors in a list of people

[Thomas_Giegeric]

Hey there

i am pretty new to AirTable and its formulas - and I am not a programming genius.
Is have a long list of people, with all kind of data. This includes the date of birth. I am trying to generate a daily number of people who are above or below 12 years of age. I played around with sorting lists a bit and managed to do 2 views - one with adults (born 4380 days ago or before that) and minors (born within the last 4380 days).

Is there a more clever way? I would actually prefer to do a new table telling me how many of which I got in my list.

Any help would be great. Thx.

[Vivid-Squid]

Hi @Thomas_Giegerich
Add this formula to find the person’s age.

DATETIME_DIFF(TODAY(),{date of birth},'years')

Then you can filter on the age.

[Thomas_Giegeric]

Thx - that helps.

Is there a way to make another table count the people of certain ages for me?

Now I got one colum with the ages of the people. How would I could adults and minors then? Can Airtable do that? Or do I have to manually look that up every day?

[kuovonne]

You could put the daily counts in a different table using a scheduled automation. If there will be less than 100 of each group, you can do this without scripting. If there might be more than 100 in either group, you should use scripting.

One possible non-scripting solution (less than 100 in each group)

• Setup a view for each group. The view filter can be based directly on the birthdate or on a formula that calculates age.
• Setup a scheduled daily automation with two “find records” actions, one for each group.
• Add a third action that creates a new record in the daily table with the automation run time and the length of the lists of records from the “find records” actions.

This is just one possible method. There are several other variations.

[Vivid-Squid]

@kuovonne mentioned some great options.

If you would like a script that will show the results, you can add the scripting app to your Base (remove the default text) and paste this:

const config = input.config({
    title: 'Member Age',
    description: 'This script shows the count of Adults and Minors',
    items: [
        input.config.table('members', {
            label: 'Members Table',
            description: 'The table in which you display the ages of Members'
        }),
        input.config.field('age', {
            label: 'Age Field',
            parentTable: 'members',
            description: 'This should be a formula field with (DATETIME_DIFF(TODAY(),{date of birth},\'years\')'
        }),
        input.config.number('cutoff', {
            label: 'Cut off age',
        }),
    ]
});
const table = config.members;
const field = config.age;
const cutoff = config.cutoff;
let result = await table.selectRecordsAsync();
let record = result.records;
let minors = [];
let adults = [];
for(let results of record){
    // console.log(results)
    let age = results.getCellValue(field);
    if (age <= cutoff){
        minors.push(results.getCellValue(field))
    }else{
        adults.push(results.getCellValue(field))
    }
}
output.markdown(`
# ${minors.length} Minors
# ${adults.length} Adults
`);

When you run it you should see: