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

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 )

)

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?

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.

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.

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!

Will do, thank you.

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!

Great!  Method 2 was more work to figure out, that's for sure!  =)

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

)

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

)