Formula Help - "Chronological Age by Month"

Hello!

Does anyone know how to calculate a “chronological age” based on date of birth? I had one that I had been using but it seems like it stopped working or changed the way it worked. I got some help from a coda employee over a year ago and it seemed to work just fine, but for some reason when I migrated to a new sheet it just didn’t work. Here’s the original formula:

If(thisRow.DOB.isblank(),"",Today().Year()-thisRow.DOB.Year()+";"+thisRow.DOB.Month())

Basically, I want age in both year and month in the following format: “YY;MM” and blank if there is no date of birth in the source column. For some reason, my numbers are off. Here’s an example:

DOB|Age
4/26/08|12;4 (should be 11;10)
4/5/09|11;4 (should be 10;10)
8/24/2010|10;8 (should be 9;6)

I am sure there is a much easier way to do this

But here is one for you for now :slight_smile:

If(thisRow.DOB.isblank(),"",RoundDown((Today()-thisRow.DOB)/365)+"; "+ Roundup(((Today()-thisRow.DOB)/365 - RoundDown((Today()-thisRow.DOB)/365))*12))

Hi,

Sorry to piggyback this thread, how would I use this formula with days as well, and also to replace ; with yrs, m and d?

Thanks!

I saw this post a while ago and it didn’t look right to me, but I forgot to try it out, until now. This formula often presents you often the correct answer, but try to do this today (febr. 24 2021) with a DOB March 3, 1995. This persons age is 25, but your formula results in 26.

There are more ways of doing this and the following formula can be written in different ways, but this one will give you the correct age:

year(Today())-
year(thisRow.DOB) -
switchif(
Month(Today())<Month(thisRow.DOB),1,
Month(Today())=month(thisRow.DOB) and day(today())<day(thisRow.DOB),1,
0)

I didn’t look at number of days following the age just yet - they are not very interesting if the age is not correct. I will look at that in a little while.

@anon68814958
You are asking about replacing the “;” with “yrs, m and d”. I would not recommend using months in this type of formula, because that gets kind of tricky since some months have more days than other months. Consider the following for 2 persons: 14 years, 6 months and 3 days if the birthday of one person is January 1st and someone else birthday is July 1st. 6 months and 3 days have a different value when you count the number of days for those 6 months and 3 days and in leap years it becomes even more complicated. Of course, it can be done with some extra formula work.
As far as getting the correct output (see my posting above) with a replacement of the “;” and adding the number of days, it is just a matter of concatenating a bunch of stuff together.

It is a bit complicated to explain in the community, but I prepared a sample that you can copy and look at the formulas. I build my formula in a couple of separate steps and then put them al together in a master formula (“one big formula”). If you are only interested in the end result, just copy the complete formula to a column in your own table - just make sure you have one date type column with the name of DOB and it should work right out of the box. When done you can rename DOB and everything should keep on working.

1 Like

as Joost indicates, many variations are possible. I
used :

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

Good morning @Christiaan_Huizer ,

Your formula is much easier than my contraption with switchif(), so I adjusted the formulas in my sample.

Kind of funny to see that changing my formula in the last column was easy because of the use of WithName(), I only had to fix something in the beginning of my formula and the whole thing worked properly.

Greetings,
Joost

1 Like