Calculating Age 3

[bdreed35]

This is my first time posting. I have looked through other forums and have found this one to be the best.

I had been looking for a function to calculate age in Crystal Reports help and the Crystal Decisions website with no luck. I came up with the following formula that seems to work fairly well so I hope it helps others.

Truncate((DateDiff ("d", {Patient.Birthdate}, CurrentDate) / 365.25))

It is necessary to use the Truncate() function, as opposed to the Round() function, because the Truncate() will drop everything to the right of the decimal while Round() will round up to the nearest whole number.

 

[crystalreporting]

People are sensitive about their age - I'd use truncate because most people don't consider themselves to be 40 until their 40th birthday i.e. even the day before (39.99999999) people would still consider themselves to be 39.

 

[synapsevampire]

Try:

DateDiff("yyyy", {Patient.Birthdate}, CurrentDate),

-k

 

[synapsevampire]

Try:

DateDiff("yyyy", {Patient.Birthdate}, CurrentDate)

-k

 

[bdreed35]

It doesn't work.

Using "yyyy" in the DateDiff function apparently only compares the years. For instance: if someone wwas born in December 1974 that current age should be 28. When using DateDiff with "yyyy" and a birthdate of 12/26/1974, it evaluates to 29.

 

[stancer]

Just the ticket - I was struggling with an identical problem, and your TIP came along just as I was at wits end.

Thank you,
stancer

 

[chelseatech]

Sorry guys,

We did a series on this a while back in Crystal Clear, and then a reader in texas told us an even better formula.

But first, why not use DateDiff? DateDiff just does the difference in years, so the difference between 12-Dec-1990 and 16-Jan-1991 is 1 year.

Dividing by 365.25 doesn't work every four years for five year olds (that was a particular problem for an insurance coy where they needed it accurate for kids no longer under their parents policy when they hit 5). Every 5 year old born in a leap year has two leap years in their age, so you really need to divide by 365.4 for them. But only if they are 5.

The best formula for age is (please memorise this as you will need it again).

if month({table.birthdate}) < month(CurrentDate) or
//Had their birthday earlier in the year
(month({table.birthdate}) = month(CurrentDate) and
(day({table.birthdate}) <= day(CurrentDate))
//Had their birthday earlier this month
Then
year(CurrentDate) - year({table.birthdate})
else
year(CurrentDate) - year({table.birthdate}) -1

Thank Charily of Texas for this great formula. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[bdreed35]

Thanks chelseatech. I need the age to be accurate so it looks like I will be using your formula going forward.

-Brian

 

[chelseatech]

The other neat thing about that formula is that it works for leap years and people with birthdays on 29 Feb. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[khughe1]

Don't forget that in place of CurrentDate, you may need to use {ReportDate} so that you get the result - "As Of" the date the report was run. There could also be any number of replacements for CurrentDate. Some organizations may need to verify and report on the age of a person when they signed up. They'd use something like {Table.SubmitDate} in place of {CurrentDate}.
Just a thought...
Nice Formula - Thanks!

 

[synapsevampire]

Ken Hamady has an interesting formula as well:

http://www.kenhamady.com/form06.html

-k