Solved

# Age formula

Posted on 2005-05-16
1,594 Views
Hi Experts,

I have this formula to compute a patient's age:

If Not IsNull({patient.birthdate}) then
ToText(Truncate((CurrentDate - {patient.birthdate}) / 365.25),0) + ' '+ {patient.gender}
else '';

The problem with this formula is that when a particular patient is just months old, the formula will return
0
The formula was intended to return the patient's age and gender in the format 12 F, 30 M, 25 F, etc.

Can anybody modify this formula so that when a patient is less than a year old, it will return how many months
old the patient is?
say, 2 months F, 3 months M, 10 months M and if a year or more older then 12 F, 30 M, 25 F, etc.

I'm using Crystal reports 8.5 without any service packs installed and MSSQL 2000 server.

yorge
0
Question by:yorge

LVL 19

Expert Comment

Why not use the DatePart Function

DateDiff ('M',{patient.birthdate} ,CurrentDate )

Gary
0

LVL 100

Expert Comment

Since you want different things displayed you need a more complex formula

NumberVar Age;
Age := DateDiff ('M',{patient.birthdate} ,CurrentDate );
If Age < 12 then
ToText(Age,0) & " months " & {patient.gender}
Else
ToText(Age/12,0) & " " & {patient.gender}

mlmcc

0

LVL 26

Expert Comment

Since DOB is calculated by rounding down based on the Month and Day of the current year, you can't just rely upon the DateDiff function.  For example, patients born 04/15/05 and 04/19/05 will both be listed as 1 month old using a standard datediff formula when the patient born 04/19/05 is really 0 months old.  The following formula calculates the correct age in months AND accounts for leap years:

NumberVar DoBVar := IIF((100 * MONTH(CURRENTDATE) + DAY(CURRENTDATE)) < (100 * MONTH({patient.birthdate}) + DAY({patient.birthdate})), 1, 0);
NumberVar AgeVar := DATEDIFF("m",{patient.birthdate},CURRENTDATE) - DobVar;
StringVar SexVar := ToText(AgeVar,0) + ' ' + {prac.SEX};

SexVar;

If you need to calculate the age in years, simply subsitute "yyyy" for "m" in the AgeVar variable.  If you don't need to concatentate the Sex to the Age, then remove the third variable and substitute AgeVar for SexVar in the fouth line.

I also have variations of these formulas as SQL Server Functions if you need them.

~Kurt
0

Author Comment

Hi All,

Rhinok: The age computation I wanted will depend on the patient's birthdate.
If 1 year old and above, I will compute the age by year say if patient's birthdate is 01/04/1973 and sex is 'M' then I'll display '32 M'
If less than a year, I will compute the age by months say if patient's birhtdate is 01/04/2005 and sex is 'F' then I'll display '4 months F'

Please provide me with a formula how to do this.

yorge
0

LVL 26

Expert Comment

Yorge, the following variation of the formula should
0

LVL 26

Accepted Solution

Sorry - hit enter when I didn't mean to - the following variation should work:

//DOBVar compares the current month and year value (517 for today) to the birthdate value (723, for example)
//If the current month year value is < the birthdate value then return 1, which will be subtracted in the MthVar and YrsVar variables
//This rounds down the year, because age is rounded down based on the month and day within the current year
//MthVar returns age in months
//YrsVar returns age in years
//AgeSex concatenates the gender to the age in months if the months are < 12, else to the age in years
//AgeSex is the value returned by the formula

NumberVar DoBVar := IIF((100 * MONTH(CURRENTDATE) + DAY(CURRENTDATE)) < (100 * MONTH({patient.birthdate}) + DAY({patient.birthdate})), 1, 0);
NumberVar MthVar := DATEDIFF("m",{patient.birthdate},CURRENTDATE) - DobVar;
NumberVar YrsVar := DATEDIFF("yyyy",{patient.birthdate},CURRENTDATE) - DobVar;
StringVar AgeSex := IIF(MthVar < 12, ToText(MthVar,0) + ' ' + {patient.gender},ToText(YrsVar,0) + ' ' + {patient.gender});

AgeSex;

~Kurt
0

## Featured Post

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…