• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1613
  • Last Modified:

Age formula

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
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.

Please Help,

1 Solution
Why not use the DatePart Function

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

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}
   ToText(Age/12,0) & " " & {patient.gender}

Kurt ReinhardtCommented:
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};


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.

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

yorgeAuthor Commented:
Hi All,

Thanks for all the reply.

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.

Kurt ReinhardtCommented:
Yorge, the following variation of the formula should  
Kurt ReinhardtCommented:
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});



Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now