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

[Eivinj]

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.

[AirOps · Accepted solution]

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.

[Eivinj]

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]

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! 🙂