Age formula

Posted on 2005-05-16
Last Modified: 2008-02-01
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,

Question by:yorge
    LVL 19

    Expert Comment

    Why not use the DatePart Function

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

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

    LVL 26

    Expert Comment

    by:Kurt Reinhardt
    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.


    Author Comment

    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.

    LVL 26

    Expert Comment

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



    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    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 ( 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…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now