Convert Age in Months to Age in Years and Months 1

[phil2k]

I have a formula field in my report which returns a persons age in months using DateDiff e.g.

Local DateTimeVar d1 := {dob_view.date1};
Local DateTimeVar d2 := {AE_PAS_DELIVERING.Attendancedate};
DateDiff ("m", d1, d2)

I need to show the age in Years and Months i.e. 3yrs 4months instead of 40 months. All help will be welcome thanks.

 

[pwesson]

Check out the MOD function, that will give you a remainder (in months) and to get the years just do a Round(Months/12, 0)

Paul Wesson, Programmer/Analyst

http://www.ipaw.net

 

[pwesson]

Exact code:

numbervar var_TotalMonths := 50;
numbervar var_Years := (var_TotalMonths / 12);
numbervar var_Months := var_TotalMonths mod var_Years;

CStr(var_Years, 0) + " Years and " + CStr(var_Months, 0) + " Months";


Paul Wesson, Programmer/Analyst

http://www.ipaw.net

 

[phil2k]

Thanks it works for people over 1yrs old fine but I keep getting a divide by zero error for anyone younger.

Any suggestions

 

[lbass]

Just a comment here. Using the datediff function to determine age may result in errors, since I think it simply subtracts monthnumbers, so that the difference between 5/31/03 and 9/01/03 = 4, and the difference between 5/01/03 and 9/30/03 also = 4. With ages, you ordinarily do not increase to the next year or month until you have reached that point--age is not generally rounded up. Ken Hamady has an FAQ on calculating age in years. I'm not sure how to adapt that so that it takes into account months, but maybe someone can jump in on that.

-LB

 

[phil2k]

I worked out a solution that works perfectly every time. if you use the ToText function on the numbervalues you can trim of the decimal places without CR rounding up and making someone a year older than they are.

The code is:

numbervar var_totalmonths := DateDiff ("m",{table.DOB},{table.DATE});
stringvar var_remainingmonths := Totext(var_totalmonths mod 12);
stringvar var_years := ToText(var_totalmonths/12);
stringvar var_yearstrimmed :=
If
Length (var_years) = 5 Then (Left(var_years,2)) Else (Left(var_years,1));
stringvar var_remainingmonthstrimmed := If Length(var_remainingmonths) = 5 Then (Left(var_remainingmonths,2)) Else (Left(var_remainingmonths,1));

Cstr(var_yearstrimmed) + "yrs " + Cstr(var_remainingmonthstrimmed) + "mths"


Try it out see if you can find any floors (I can't) any improvements will be graciously received

Phil