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.
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.
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 yearIF((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! 🙂
