I was trying to help someone write a formula to accurately calculate age (from birth to death) in years and months (the result would look like 32y/11m) and found scores of Internet postings claiming to have a good formula. But I found most of them to have issues with specific records. So I tried my own solution. I think it works, but can others in the forum give some input and perhaps test this? What follows is my explanation and the formula:
Calculating a personu2019s age in only months and years can be a problem if precision is necessary since some methods will round a number up and other methods utilize the average number of days in a year to account for leap year. Both of these will cause some results to be incorrect.
A key issue is whether or not the anniversary of a personu2019s birth has been passed in the second date, whether death or current date. Capitation and HEDIS reports require accurate ages in years and months. The following table shows how the calculated age can change in relationship to the birth anniversary date.
Month Scenario (dependent on day)
Death: 8/25/---- Birth: 12/1/---- 8 months
Death: 8/1/---- Birth: 12/25/---- 7 months
Year Scenario (dependent on month)
Death: 8/1/2008 Birth: 12/1/2000 7 years
Death: 12/1/2008 Birth: 8/1/2000 8 years
To correct this, if the death day is less than the birth day, the month value will be lowered by one; if the death month is less than the birth month, the year value will be lowered by one.
A second issue can occur if the birth day was February 29 (on a leap year) and the death day was February 28 (on a non leap year). The first argument in the u201CCfDayValuesu201D variable tests for this.
A third issue can occur if the birth day was the 31st in a 31-day month and the death day was the 30th in a 30-day month. The core methodology for handling the key issue above will miscalculate this scenario. The first argument in the u201CCfDayValuesu201D variable also tests for this.
Local BooleanVar CfDayValues := IF ((Month ({@Date of Birth}) * 100) + Day ({@Date of Birth})) IN [131, 229, 331, 531, 731, 831, 1031, 1231] AND ((Month ({@Date of Death}) * 100) + Day ({@Date of Death})) IN [228, 430, 630, 930, 1130] THEN False
ELSE
(DateTime (1920, 01, Day ({@Date of Death}))) < (DateTime (1920, 01, Day ({@Date of Birth})));
Local BooleanVar CfMoValues :=
(DateTime (1920, Month ({@Date of Death}), 01)) < (DateTime (1920, Month ({@Date of Birth}), 01));
Local NumberVar CfDay := IIF (CfDayValues, 1, 0);
Local NumberVar CfMonth := IIF (CfMoValues, 1, 0);
Local NumberVar AgeMonths := (DateDiff('m', {@Date of Birth}, {@Date of Death}) - CfDay) Mod 12;
Local NumberVar AgeYrs := DateDiff('yyyy', {@Date of Birth}, {@Date of Death}) - CfMonth;
Local StringVar YrsMos := ToText(AgeYrs, 0) + 'y/' + ToText(AgeMonths, 0) + 'm';
YrsMos;
//The list of numbers above inside the formula should be in square brackets, but when I posted this, the brackets disappeared and the numbers turned blue. I am not sure how to fix it in the posting.
Edited by: Ray Perkins on Jul 19, 2008 10:50 PM