×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Business Objects: Crystal Reports 1 Formulas FAQ

## Formula Help

 How do I accurately calculate age in years, months or years and months? by rhinok faq767-5876 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/2005DATEDIFF("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/2005DATEDIFF("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 YearsNumberVar 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 MonthsNumberVar 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 MonthsNumberVar 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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!