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.