Crystalkeen logo; we offer Crystal reports viewers and schedulers
 
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

  Resources  

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
 

CrystalReports
on Steroids

Crystal Reports: Age Calculation

Birthday formula (in this year). A good database design never includes data that requires continual user update. You should never store age as a field in your db, as sometime in the next year it will change.

Instead, store the person’s birth date and you can accurately calculate their age when you need it. This field also enables you to calculate age at any date in the past or future. The starting point is identify when the birthday occurs this year with:

Date(year(CurrentDate),month({Employee.Birthdate}), day({Employee.BirthDate})

This formula has a problem if the birthday is on 29 February and the year isn’t a leap year, so you need to handle that condition with:

If month({Employee.Birthdate}) = 2 and day({Employee.BirthDate})=29

Then Date(Year(CurrentDate),3,1) –1 else

Else Date(year(CurrentDate),month({Employee.Birthdate}), day({Employee.BirthDate})

We don’t need to test for a leap year – just go to the day before 1 st of March if their birthday is the 29th of February. Now we have a formula {@birthday this year} we can use to calculate an accurate age (below).

Accurate method to calculate age (in years). If you subtract two date fields from each other, you get the number of days between two dates. Dividing by seven will accurately determine how many weeks between the dates, and you can use DateDiff to calculate the number of months if you have version Crystal Reports 8.0 or above.

The DateDiff function can also identify the number of years as one of its options. If you want to calculate an accurate age, we have seen a range of methods used. After much experimentation, we have developed this method:

If {@birthday this year} < CurrentDate Then Year(CurrentDate)- year({Employee.BirthDate})

Else Year(CurrentDate)- year({Employee.BirthDate})-1

This formula uses the previous hint to identify when the birthday is this year, and compares it to the CurrentDate. If they have had their birthday this year, then the difference in years is their age. If they haven’t had their birthday, then you need to subtract one from that value. But wait, we have some other ways to calculate this….

 

Method #2 to calculate age (in years). In the tech tips on hammerman.com, Yelena Lakhter and Ken Hardwick contribute a formula that combines our two hints on the previous page. The original formula compared the birthdate and the current date. The complicated bit is to allow for a birthday on February 29 in a leap year.

// birthday feb 29 and current year is leap year

if month({TABLE.DATEFIELD})=2 and day({TABLE.DATEFIELD})=29 and

day(date(year(CurrentDate),3,1)-1)=28

then

if date(year(CurrentDate),month({TABLE.DATEFIELD}),day({TABLE.DATEFIELD})- 1) > CurrentDate

then year(CurrentDate)-year({TABLE.DATEFIELD})-1

else year(CurrentDate)-year({TABLE.DATEFIELD})

else

// birthday not feb 29 or current year is a leap year

if date(year(CurrentDate),month({TABLE.DATEFIELD}),day({TABLE.DATEFIELD})) > CurrentDate

then year(CurrentDate)-year({TABLE.DATEFIELD})-1

else year(CurrentDate)-year({TABLE.DATEFIELD})

This illustrates how complex a crystal reports formula can be, and is an excellent illustration of nested if.. then.. else..

 

Method #3 to calculate age (in years). Just to show how flexible Crystal reports can be. Here is another approach from Ken Harmady to calculate an accurate age. This technique could be useful in other problems as well. Rather than working out the actual day the birthday is due this year, we just need to know if the birthday has occurred or not. Ken calculates a number using the month and the day of the current date and the birth date. The number will look like MMDD (or 1015 for the 15 th of October), but in fact any multiplier greater than 32 will be acceptable.

WhileReadingRecords;

DateVar Birth:= {table.Birthdate}; // Replace this with your field for Date Of Birth

DateVar Ann := CurrentDate; // to get their age as of the time of the report or

//or the date field of an event to get their age as of the time of that event.

if (Month(Ann) * 100) + Day (Ann) >= (Month(Birth) *100) + Day (Birth)

then Year (Ann) - Year(Birth)

else Year (Ann) - Year(Birth) -1

This hint is on Ken Harmady’s site at kenhamady.com Ken is a Crystal Reports instructor and has several pages of useful formulas and tips on his site. He is also a regular contributor to the Crystal Reports forums and a leading Tip Master on tek-tips.com. Have a look at that site for hints and tips on a wide range of subjects.

 

Method #4 to calculate age (in years).

Last month (Issue 22/Sep-Oct 2001) we showed you several ways to calculate an accurate age from a date field. Charily Nash (from Nashville, TN) suggests some of us like to do things the hard way. Here is her formula:

if Month ({table.datefield}) < Month (CurrentDate) or //birthday in prior month

(Month ({table.datefield}) = Month (CurrentDate) and //or birthday this month

Day ({table.datefield}) <= Day (CurrentDate)) // and one day prior this month

then Year (CurrentDate) - Year ({PATIENT.DOB}) // have a birthday this year

else (Year (CurrentDate) - Year ({PATIENT.DOB})) – 1 // subtract one from the year difference

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.

Authorship

Except where an author's name is given at the start of the article, all of these articles were written by Mo Naughton or Bruce Ferguson and edited by Mark Lamendola. Mo is a Crystal Reports consultant, trainer, and developer for Chelsea Technologies, Inc. Bruce Ferguson is a Crystal Reports consultant, trainer, and developer for CrystalKiwi, Inc. Mark Lamendola is a writer and editor with over 15 years experience in professional and trade publications.  

 

 

  • Questions? Please write to mark@crystalkeen.com. We do want your business.
  • Do you have your own tips for Crystal Reports administrators and designers? Write to mark@crystalkeen.com and we'll post your tips with a link to your Website (or with some other attribution if you choose).
  • Crystal Reports is a subsidiary of Business Objects, which is owned by SAP.