Help

Calculate Age At Time of Release

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
691 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Hannah_Martin
5 - Automation Enthusiast
5 - Automation Enthusiast

This has probably been answered before, but I'm not sure how formulas work or how to maniuplate them to fit my use case.

My base has multiple tables, one of which is a Members table. This includes the person's name as the primary field and other info, including their birthdate, in other fields.

On another table, I have a list named Releases. Each Release record links to the Members table, and includes every Member who participated in a Release. Also, each Release has a release date field.

As an example (Note that I'm only interested in linking to the H!P Members field, not the Non-H!P Members field):

2023-03-10 17_10_49.png

Let's use the record named Morning Coffee to hopefully explain things well enough.

Morning Coffee was released 1998-01-28, and five H!P Members participated in the release: Nakazawa Yuuko, Ishiguro Aya, Iida Kaori, Abe Natsumi, Fukuda Asuka. I would like to have a field running a formula that displays each individual member's age in years on that date, preferably in a list. As I still plan on having the field linking to the H!P Members table, the direct link in the formula output isn't necessary, but I would like that option if possible.

So whatever the formula would be would throw up a result like this:

  • {Nakazawa Yuuko}: {Age in Years on 1998-01-28}
  • {Ishiguro Aya}: {Age in Years on 1998-01-28}
  • {Iida Kaori}: {Age in Years on 1998-01-28}
  • {Abe Natsumi}: {Age in Years on 1998-01-28}
  • {Fukuda Asuka}: {Age in Years on 1998-01-28}

Or, for another release:

  • {Heike Michiyo}: {Age in Years on 1998-03-25}

The number of Members in a Release can be anywhere from one to almost 70 (I have a total of 283 records in the H!P Members table for context), so I need it to pull from the Birth Date field in the H!P Members table directly, then calcuate that data against the Release Date field in the Releases table.

I really can't seem to wrap my head around formulas no matter how hard I try! I feel like this is a very easy one, but I have no idea what I'm doing.

If this doesn't make any sense I do apologise!

1 Solution

Accepted Solutions
TheTimeSavingCo
17 - Neptune
17 - Neptune

Unfortunately I don't think you're missing anything and the only way I can think of handling this is via a junction table.  We'd create one record per album <> member variant, and then use that to calculate their age at the time of the album's release date:

Screenshot 2023-03-10 at 4.22.22 PM.png

Screenshot 2023-03-10 at 4.18.15 PM.png

Screenshot 2023-03-10 at 4.18.12 PM.png

Link to base

With one record per album<>member variation created in the "For Age Calculation" table, we can create lookup fields to show the member's birthday and the album's release date, and thus can use a formula field to calculate the age.  We then use a rollup field to pull that data back into the "Albums" table

And the records are created via an automation that's triggered when we tick the checkbox, and you can view how the automation's set up in the table as well

See Solution in Thread

2 Replies 2
TheTimeSavingCo
17 - Neptune
17 - Neptune

Unfortunately I don't think you're missing anything and the only way I can think of handling this is via a junction table.  We'd create one record per album <> member variant, and then use that to calculate their age at the time of the album's release date:

Screenshot 2023-03-10 at 4.22.22 PM.png

Screenshot 2023-03-10 at 4.18.15 PM.png

Screenshot 2023-03-10 at 4.18.12 PM.png

Link to base

With one record per album<>member variation created in the "For Age Calculation" table, we can create lookup fields to show the member's birthday and the album's release date, and thus can use a formula field to calculate the age.  We then use a rollup field to pull that data back into the "Albums" table

And the records are created via an automation that's triggered when we tick the checkbox, and you can view how the automation's set up in the table as well

Hannah_Martin
5 - Automation Enthusiast
5 - Automation Enthusiast

Sorry for taking so long to reply (I genuinely forgot I posted this! Whoops!), but thank you for your response! I wondered if it was a bit too complicated for Airtable to do in the way I wanted, although it looks like there is some kind of work around. I think I'll leave it alone for now, since there are thousands of records I'd want to do this with and I'd like to minimise the number of records I have in my base because I'll eventually hit the 50,000 mark.

Thanks heaps, I really appreciate it, and sorry again for taking so long to reply! That's entirely on me.