Skip to main content
Solved

Calculating a childs age at a given date + returning detailed info

E

Hello,

as the subject says i'm looking to write a formula which returns a childs age at 1. august 2023 based on their birthday. i'm currently using a formula which gives me some info, but i would really like to know if there are other possibilities. 

This is what i use today:


DATETIME_DIFF(
{Planlagt overgang},
{Født},
'months'
)

I use months because years is not sufficient in detail - since i need more information.
Is it possible to get "1 year and 2 months" or similar instead of "14"?

Bonus task: how to return blanks instead of NaN.

Best answer by AirOps

Hi Eivinj, 

Try using a formula like this: 

IF({Planlagt overgang},
CONCATENATE(
ROUNDDOWN(DATETIME_DIFF({Født}, {Planlagt overgang}, "months")/12,0),
"Y ",
DATETIME_DIFF({Født}, {Planlagt overgang}, "months")-(12*ROUNDDOWN(DATETIME_DIFF({Født}, {Planlagt overgang}, "months")/12,0)),
"M")
)
 
 
Index: 
Finds age in years
Finds the number of months remaining after subtracting the years in months
prevents NaN by only executing on records that have a birthday entered
 
I hope this helps! Please let me know if this solves your issue.
View original
Did this topic help you find an answer to your question?

3 replies

AirOps
  • AirOps
  • Participating Frequently
  • 57 replies
  • Answer
  • February 24, 2023

Hi Eivinj, 

Try using a formula like this: 

IF({Planlagt overgang},
CONCATENATE(
ROUNDDOWN(DATETIME_DIFF({Født}, {Planlagt overgang}, "months")/12,0),
"Y ",
DATETIME_DIFF({Født}, {Planlagt overgang}, "months")-(12*ROUNDDOWN(DATETIME_DIFF({Født}, {Planlagt overgang}, "months")/12,0)),
"M")
)
 
 
Index: 
Finds age in years
Finds the number of months remaining after subtracting the years in months
prevents NaN by only executing on records that have a birthday entered
 
I hope this helps! Please let me know if this solves your issue.
AirOps Consulting | Course: Airtable Mastery
E
1 person likes this
  • E
    Eivinj
E
  • Eivinj
  • Author
  • New Participant
  • 1 reply
  • February 24, 2023

Thank you! This worked like a charm.
I had to reverse the order of the fields(use Planlagt overgang first, then Født) in order to return positive numbers. As the original solution returned negative numbers.

Now, a next level task:
one child counts as two, until they are 3 years old, then they count as one.
However, they count as one from august in the year that they celebrate their third birthday. And in addition to that, i want the "count" to be based on {Planlagt overgang} and obviously their birthday.

For example:
If a child is 2 years now, they count two "spots".
The child has birthday in october 2023.
As soon as 1. august passes - they count as "one spot"

What this means is that i will know when a child counts as one when the planned class switch is scheduled 🙂 god this gott messy.

AirOps
  • AirOps
  • Participating Frequently
  • 57 replies
  • February 24, 2023

Hi Eivinj, 

I understood your conditions to mean that as soon as child turn 3 they count as 1 and for children born after august 1st 2023 they count as 1  after august 1st 2023. 

Typically went I solve complex (and messy) problems like this I break out the formula into tangible steps in different fields, which helps me keep track of solving the issue. I will share my process as I think it will be helpful. 

Below is a screenshot of my base: 

I used "Today's Date" as a regular date field so I could test the different conditions, in reality you can replace this field in all the formulas it shows up in with the function TODAY( ). I also changed {Planlagt overgang} to todays date in "Age" to help identify what the desired result should be. The final field that computes the result you would like is "Count". 

Here is a list of what each field is and their respective formula:

Født Date of Birth (date field)

Today's Date date field (explained above)

Age

IF({Født},CONCATENATE(ROUNDDOWN(DATETIME_DIFF({Todays Date}, {Født}, "months")/12,0), "Y ",DATETIME_DIFF({Todays Date}, {Født}, "months")-(12*ROUNDDOWN(DATETIME_DIFF({Todays Date}, {Født}, "months")/12,0)),"M"))
 
Planlagt overgang transition date
 
Desired Result what count should return if working correctly
  
Turn 3 this year?  Checks if child turns three this year
IF((DATETIME_FORMAT({Todays Date}, "YYYY") - DATETIME_FORMAT({Født}, "YYYY")) = 3, 1, 0)
 
Younger than 3 this year? Checks if child is younger than 3
IF((DATETIME_FORMAT({Todays Date}, "YYYY") - DATETIME_FORMAT({Født}, "YYYY")) < 3, 1, 0)
 
Born in first 7 months of year? Checks to see if child is born Jan-July
IF(DATETIME_FORMAT({Født}, "MM")<8, 1, 0)
 
Born <7, has Birthday passed? Checks to see if the child's birthday is today has passed
IF(DATEADD({Født},3, "years")<={Todays Date}, 1, 0)
 
Born >= 7, has Planlagt passed? Checks to see if the transition date is today or has passed
IF({Todays Date}>={Planlagt overgang}, 1, 0)
    
Count finds our desired result (puts it all together)
IF({Født},
IF({Turn 3 this year?}=1,
IF({Born in first 7 months of year?}=1,
IF({Born <7, has Birthday passed?}=1, "1", "2")
,
IF({Born >= 7, has Planlagt passed?}=1, "1","2")
)
,
IF({Younger than 3 this year? }=1, "1","2")
)
)
 
As you can see the final formula is pretty clean and easily understandable. You can combine this into one very large and complex formula by replacing the fields call outs in the count formula with their actual formulas. 
 
I hope this helps! 🙂 

 

 

 

 

AirOps Consulting | Course: Airtable Mastery

Reply


Most helpful members this week

Terms & Conditions