INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...Over the past year I have found your site to be EXCELLENT. Never have I been able to find so many answers to such vast problems and it is an excellent service..."
Geography
Where in the world do Tek-Tips members come from?
|
Business Objects: Crystal Reports 1 Formulas FAQ
|
Formula Help
|
How do I accurately calculate age in years, months or years and months?
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
|
|
 |
|
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!
Already a member? Close this window and log in.
Join Us Close
