INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Formula Help

How do I accurately calculate age in years, months or years and months? by rhinok
Posted: 17 May 05 (Edited 17 May 05)

The concept of calculating age correctly is complicated. Age is rounded down based on both the month and day within the target year. Also, how do you account for leap years?  Thankfully, the formulas for correctly calculating age are fairly simple.

Why is it so important to calculate age correctly instead of 'mostly correctly'?  If you're reporting claims encounter information and/or demographics to a government agency such as CMS (Center for Medicaid Services), for example, and you depend upon reimbursement then calculating age correctly is extremely important!

I have seen several methods for calculating age.  Unfortunately, most of them are flawed. For example, the standard DATEDIFF function doesn't round down the age. The following DATEDIFF function returns a value of 1 for a persons age in months when the true value should be 0:

//Date of Birth is 04/17/2005
//Target Date is 05/18/2005
DATEDIFF("m",#04/17/2005#,#05/18/2005#)

Likewise, the following DATEDIFF function returns a value of 1 for a persons age in years when the true value should be 0:

//Date of Birth is 04/17/2004
//Target Date is 05/18/2005
DATEDIFF("yyyy",#04/17/2004#,#05/18/2005#)

The results from the above formulas would be especially problematic for HEDIS and Capitation reports, for example...  HEDIS reports categorize patients by Age and Sex, as do capitation reports.  Physicians' capitation payments are typically based on age/sex criteria and their payments can be drastically affected by incorrect categorization.  

Other common methods of calculating age include averaging the number of days in a year in an effort to account for leap years.  This method is also flawed.  The math simply doesn't work when the number of days are averaged.  Most of the ages will be correct, but not all of them...

Following are formulas for correctly calculating a persons age in years, months and in years and months.  The premise for each formula is simple - if the numeric value for the current month and year combination (517 for 05/17/2005) is < the numeric value for the month and year combination of the birthdate (723 for 07/23/2001) then return 1, else 0.  This value will then be subtracted from the standard DATEDIFF result - accurately rounding down the year or months, based on the Month and Day.  Leap years aren't an issue because they are handled by the DateDiff portion of the formula and are irrelevent in the month/day value comparisons.

//Age in Years
NumberVar DoBVar := IIF((100 * MONTH(CURRENTDATE) + DAY(CURRENTDATE)) < (100 * MONTH({person.birthdate}) + DAY({person.birthdate})), 1, 0);
NumberVar YrsVar := DATEDIFF("yyyy",{person.birthdate},CURRENTDATE) - DobVar;

YrsVar;

//Age in Months
NumberVar DoBVar := IIF((100 * MONTH(CURRENTDATE) + DAY(CURRENTDATE)) < (100 * MONTH({person.birthdate}) + DAY({person.birthdate})), 1, 0);
NumberVar MthVar := DATEDIFF("m",{person.birthdate},CURRENTDATE) - DobVar;

MthVar;

//Age in Years and Months
NumberVar DoBVar := IIF((100 * MONTH(CURRENTDATE) + DAY(CURRENTDATE)) < (100 * MONTH({person.birthdate}) + DAY({person.birthdate})), 1, 0);
NumberVar MthVar := (DATEDIFF("m",{person.birthdate},CURRENTDATE) - DobVar) MOD 12;
NumberVar YrsVar := DATEDIFF("yyyy",{person.birthdate},CURRENTDATE) - DobVar;
StringVar MthYrs := TOTEXT(YrsVar,0) + " years " + TOTEXT(MthVar,0) + " months";

MthYrs;

I hope this helps you!  Good luck and have fun!

~Kurt

Back to Business Objects: Crystal Reports 1 Formulas FAQ Index
Back to Business Objects: Crystal Reports 1 Formulas Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close