Is there a way to calculate age in years, months & days?

• 0
• Question
• Updated 1 year ago

I currently use a formula, which works fine, to calculate age in years. Going forward I need to calculate years, months & days. This is the formula I'm using now:

var Number BirthYear = Year ([Date of Birth]);

var Number BirthMonth = Month ([Date of Birth]);

var Number BirthDay = Day ([Date of Birth]);

var Number CurrentYear = Year(Today ());

var Number CurrentMonth = Month(Today ());

var Number CurrentDay = Day(Today ());

var Text BirthMonthTiming = If(\$BirthMonth < \$CurrentMonth, "Earlier",

If(\$CurrentMonth=\$BirthMonth, "Same", "Later"));

var Bool LaterDayOfMonth = \$CurrentDay < \$BirthDay;

Case (\$BirthMonthTiming,

// if the BirthDate is in an earlier month, then age is this year - birthyear

"Earlier",\$CurrentYear - \$BirthYear,

// if not in an earlier month, see if it's in the same month;  if so, we

//     have to check the date

"Same",If (\$LaterDayOfMonth, \$CurrentYear - \$BirthYear - 1, \$CurrentYear - \$BirthYear),

"Later", \$CurrentYear - \$BirthYear - 1)

• 180 Points

Posted 3 years ago

• 0

Xavier Fan, Champion

• 630 Points
Try this:

Fields

1)  [Date of Birth] - Date field

2)  [Age - Duration] - Formula Duration field

with formula: Today() - [Date of Birth]

3)  [Age - Years] - Formula Numeric field

with formula: Floor ( ToDays([Age - Duration]) / 365 )

4)  [Age - Months] - Formula Numeric field

with formula: Floor ( Mod ( ToDays([Age - Duration]), 365 ) / 30 )

5)  [Age - Days] - Formula Numeric field

with formula: ToDays([Age - Duration]) - ([Age - Years] * 365) - ([Age - Months] * 30)

The [Age - Duration] field gives you the Duration for the age (between [Date of Birth] and today).

Once you have that duration, you can apply it how you want.

Here - the [Age - Years], [Age - Months], [Age - Days] gives you the number of year / month / days for that duration (assuming 365 days in a year, 30 days in a month).

e.g.

[Date of Birth] = 1/1/2016

[Age - Duration] = 13 Days

Age = 0 Years, 0 Months, 13 Days

[Date of Birth] = 1/1/1980

[Age - Duration] = 13162 Days

Age = 36 Years, 0 Months, 22 Days

etc. ======

Method 2

This method "counts" the years and months as a whole, regardless of leap years, or months of 28/29/30/31 days.

This avoids some of the issues with Method 1 with edge cases.

1)  [Date of Birth] - Date field

2)  [Age - Years] - Formula Numeric field - with formula:

var Date BirthdayMonthDay = Date(Year( Today() ), Month([Date of Birth]), Day([Date of Birth]));

var Date OneYearOldBirthday = AdjustYear( [Date of Birth], 1);

If (

Today() < \$OneYearOldBirthday, 0,

\$BirthdayMonthDay > Today(), Year (Today() ) - Year ([Date of Birth]) - 1,

Year (Today() ) - Year ([Date of Birth])

)

3)  [Age - Months] - Formula Numeric field - with formula:

var Date OneMonthOldBirthday = AdjustMonth( [Date of Birth], 1);

var Number TodayMonth = Month( Today() );

var Number TodayDay = Day( Today() );

var Number BirthdayMonth = Month( [Date of Birth] );

var Number BirthdayDay = Day( [Date of Birth] );

If (

Today() < \$OneMonthOldBirthday, 0,

\$TodayMonth = \$BirthdayMonth  and  \$TodayDay >= \$BirthdayDay, 0,

\$TodayMonth = \$BirthdayMonth  and  \$TodayDay < \$BirthdayDay, 11,

\$TodayDay = \$BirthdayDay and \$TodayMonth > \$BirthdayMonth, \$TodayMonth - \$BirthdayMonth,

\$TodayDay = \$BirthdayDay and \$TodayMonth < \$BirthdayMonth, \$TodayMonth + 12 - \$BirthdayMonth,

\$TodayMonth > \$BirthdayMonth  and  \$TodayDay > \$BirthdayDay, \$BirthdayMonth - \$TodayMonth - 1,

\$TodayMonth > \$BirthdayMonth  and  \$TodayDay < \$BirthdayDay, \$BirthdayMonth - \$TodayMonth,

\$TodayMonth < \$BirthdayMonth  and  \$TodayDay > \$BirthdayDay, \$TodayMonth + 12 - \$BirthdayMonth,

\$TodayMonth < \$BirthdayMonth  and  \$TodayDay < \$BirthdayDay, \$TodayMonth + 11 - \$BirthdayMonth

)

4)  [Age - Days] - Formula Numeric field - with formula:

var Number TodayMonth = Month( Today() );

var Number TodayDay = Day( Today() );

var Number BirthdayMonth = Month( [Date of Birth] );

var Number BirthdayDay = Day( [Date of Birth] );

var Date OneMonthBeforeToday = AdjustMonth(Today(), -1);

var Date BirthdayDayOneMonthBeforeToday = Date( Year(\$OneMonthBeforeToday), Month(\$OneMonthBeforeToday), \$BirthdayDay);

If (

Today() <= [Date of Birth], 0,

\$TodayDay = \$BirthdayDay, 0,

\$TodayDay > \$BirthdayDay, \$TodayDay - \$BirthdayDay,

\$TodayDay < \$BirthdayDay, ToDays( Today() - \$BirthdayDayOneMonthBeforeToday )

)
• 180 Points
Thanks Xavier! That worked. But some of the ages are off by 3 or 4 days, is there any way to adjust for Leap Years?

Xavier Fan, Champion

• 630 Points
I think just from the assumptions of 365 days in a year, and 30 days in a month - you'll get some variance of a few days (depending on which months you count as 28, 29, 30, 31 days, etc.).

i.e. If you say the age is 10 years, 6 months, 3 days - which 6 months you are talking about would affect the ages by a few days either way.

If you can describe an alternate method of calculating the age in years / months / days that would match what you want, we can look at translating that into QuickBase formulas.
• 180 Points
Hi Xavier, sorry for the delay in responding I was off for a few days. I thought to try for the same accuracy as the duration calculators I've used online. I have one age (DOB 1/27/2006) coming out as 9 yrs. 12 months & 0 days which looks odd on a report.

Xavier Fan, Champion

• 630 Points
I updated the answer above with "Method 2" - to take care of some of the edge cases like these.  Let me know how it works out for you!
• 180 Points
Will do, thank you.
• 180 Points
Method 2 worked perfectly! Got exactly the same results as I did from the online Date/Duration calculators I've been using. Thanks again Xavier!

Xavier Fan, Champion

• 630 Points
Great!  Method 2 was more work to figure out, that's for sure!  =)
• 320 Points
I had to modify one of the formulas, it was giving negative numbers.  Here is my revised formula

3)  [Age - Months] - Formula Numeric field - with formula:

var Date OneMonthOldBirthday = AdjustMonth( [Date of Birth], 1);

var Number TodayMonth = Month( Today() );

var Number TodayDay = Day( Today() );

var Number BirthdayMonth = Month( [Date of Birth] );

var Number BirthdayDay = Day( [Date of Birth] );

If (

Today() < \$OneMonthOldBirthday, 0,

\$TodayMonth = \$BirthdayMonth  and  \$TodayDay >= \$BirthdayDay, 0,

\$TodayMonth = \$BirthdayMonth  and  \$TodayDay < \$BirthdayDay, 11,

\$TodayDay = \$BirthdayDay and \$TodayMonth > \$BirthdayMonth, \$TodayMonth - \$BirthdayMonth,

\$TodayDay = \$BirthdayDay and \$TodayMonth < \$BirthdayMonth, \$TodayMonth + 12 - \$BirthdayMonth,

\$TodayMonth > \$BirthdayMonth  and  \$TodayDay > \$BirthdayDay, \$TodayMonth - \$BirthdayMonth,

\$TodayMonth > \$BirthdayMonth  and  \$TodayDay < \$BirthdayDay, \$TodayMonth - \$BirthdayMonth,

\$TodayMonth < \$BirthdayMonth  and  \$TodayDay > \$BirthdayDay, \$TodayMonth + 12 - \$BirthdayMonth,

\$TodayMonth < \$BirthdayMonth  and  \$TodayDay < \$BirthdayDay, \$TodayMonth + 11 - \$BirthdayMonth

)
• 320 Points
Oops, one more change...

I had to modify one of the formulas, it was giving negative numbers.  Here is my revised formula

3)  [Age - Months] - Formula Numeric field - with formula:

var Date OneMonthOldBirthday = AdjustMonth( [Date of Birth], 1);

var Number TodayMonth = Month( Today() );

var Number TodayDay = Day( Today() );

var Number BirthdayMonth = Month( [Date of Birth] );

var Number BirthdayDay = Day( [Date of Birth] );

If (

Today() < \$OneMonthOldBirthday, 0,

\$TodayMonth = \$BirthdayMonth  and  \$TodayDay >= \$BirthdayDay, 0,

\$TodayMonth = \$BirthdayMonth  and  \$TodayDay < \$BirthdayDay, 11,

\$TodayDay = \$BirthdayDay and \$TodayMonth > \$BirthdayMonth, \$TodayMonth - \$BirthdayMonth,

\$TodayDay = \$BirthdayDay and \$TodayMonth < \$BirthdayMonth, \$TodayMonth + 12 - \$BirthdayMonth,

\$TodayMonth > \$BirthdayMonth  and  \$TodayDay > \$BirthdayDay, \$TodayMonth - \$BirthdayMonth,

\$TodayMonth > \$BirthdayMonth  and  \$TodayDay < \$BirthdayDay, \$TodayMonth - \$BirthdayMonth -1,

\$TodayMonth < \$BirthdayMonth  and  \$TodayDay > \$BirthdayDay, \$TodayMonth + 12 - \$BirthdayMonth,

\$TodayMonth < \$BirthdayMonth  and  \$TodayDay < \$BirthdayDay, \$TodayMonth + 11 - \$BirthdayMonth

)