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

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered

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)

Photo of Mike

Mike

  • 40 Points

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
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 )

)
Photo of Mike

Mike

  • 40 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?
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
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.
Photo of Mike

Mike

  • 40 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.
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
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!
Photo of Mike

Mike

  • 40 Points
Will do, thank you.
Photo of Mike

Mike

  • 40 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!
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
Great!  Method 2 was more work to figure out, that's for sure!  =)
Photo of John Freire

John Freire

  • 196 Points 100 badge 2x thumb
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

)
Photo of John Freire

John Freire

  • 196 Points 100 badge 2x thumb
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

)