Calculating a person's age?

Hi @Jean_Goodwin
Have you tried some thing like this -

Round( Today() - birthday)/365, 0)

2 Likes

But leap year! Although, 365.24… whatever should therefore work. Thanks!

Accurately calculating age in all cases is a surprisingly tricky challenge. For example, take a look at this Java example - it’s way bigger than you would expect!

Since we just need age in years, not in years + months + days, so it will be just the result of years subtraction. And minus one if there was no birthday this year yet.

Formula:

Today().Year() - thisRow.Birthday.Year() - If(Date(Today().Year(), thisRow.Birthday.Month(), thisRow.Birthday.Day()) > Today(), 1, 0)

Example:

7 Likes

Nice, thanks! Not only does it solve the problem, it aids my continuing education in formula-language.

What is the advantage of doing this versus what was suggested above?

Round((Today() - birthday)/365.24, 0)

Asking because I’m always wanting to simplify my formulas and keep things clean, but striving for accuracy of course.

Round is not right formula because it rounds to closest. I am 29, my birthday is 23rd of November, 1989. Your formula gives me 30 because I am closer to 30 than 29.

Okay, maybe we should switch to Floor?

Floor((Today() - birthday)/365.24, 1)

However, there are still some pairs of dates which will give not right answer. E.g.: today is the 8th of July, 2019. If today is my birthday, and I am born in 1989, this formula still gives me 29, when I am technically already 30. It happens because the average number of days in year depends on specific start and end year we take.

Sometimes you just have to write the way you do it mentally. :stuck_out_tongue_winking_eye:

@Denis_Peshekhonov any guesses?

2019-07-09%2016_10_54-Bus%208%20Roster

It’s strange! Can you share the doc?

I can’t because of the sensitive information that’s involved. My birthday column is formatted as a Date if that helps. Otherwise I copied and pasted your formula into my doc.

So, try to rewirite this fomula by hand :slight_smile: If this will not help, try to decompose it and check different parts if they work.

1 Like

I ended up with:

If(thisRow.Birthday.IsNotBlank(),Today().Year()-thisRow.Birthday.Year() - If(Date(Today().Year(),thisRow.Birthday.Month(),thisRow.Birthday.Day())>Today(),1,0) ,"")

For future reference: I wrapped the entire formula in an “if” statement to leave the field blank if the input data is blank, in this case “Birthday.” Otherwise for blank fields the output would be Today().Year()

3 Likes

Cool, thanks! Yes, I forgot about blank values.

Excellent thread! I’m curious if someone could help me with this formula (recommended above) with the addition of a “Died on” date column?

If(thisRow.Birthday.IsNotBlank(),Today().Year()-thisRow.Birthday.Year() - If(Date(Today().Year(),thisRow.Birthday.Month(),thisRow.Birthday.Day())>Today(),1,0) ,"")

I’m unsure where in the statement it would be best to include that factor.

1 Like

Hi @Alice_Packard,

I guess that a good solution would be having a Date column Last Day containing the formula:
Died on.ifBlank(Today())
(assuming you also have a Died On column, of course)

Then, replace all the "Today()"s in the given formula with Last Day.

I hope this helps.
Cheers.

1 Like

Excellent! Thank you so so much Federico, much appreciated. Having both a “Died on” and “Last Day” column is really clever, thanks for the tip. The formula works as expected :slight_smile:

1 Like

This was good, but I added “Need DOB” Rather than leave it blank just for fun.

This makes no sense unless it’s written out. Too confusing.

This is correct. Thank you!