Monday, October 24, 2011

Crystal Reports : Calculating Age

One of the many things you will want to do with a bona-fide date field is calculate age.
Crystal makes it easy for us here; as long as we're comparing two date type fields, we can perform simple math.
Let us suppose that the date field in question is {CONTACTS.SOURCEDATE}, which represents when a particular prospect was imported into our database.

1. We can see how many records were imported in the last seven days

{CONTACTS.SOURCEDATE} <= CurrentDate() - 7

2. Or how "old" any particular record is, expressed in days

CurrentDate() - {CONTACTS.SOURCEDATE}

3. To calculate the age in years, we must use the Crystal DateDiff function:

DateDiff("yyyy", {CONTACTS.SOURCEDATE}, CurrentDate())

The "yyyy" specifies we want the answer in years; "m" and "d" are also options (months and days, correspondingly)

2 comments:

Anonymous said...

The exact working code is:-
If (Not(isNull({DataTable2.dbirth}))) Then
ToText(int(DateDiff('d',DATE({DataTable2.dbirth}),CurrentDate())/365.25),0)+' Years '+
ToText(int(remainder(DateDiff('d',DATE({DataTable2.dbirth}),CurrentDate()),365.25)/30),0)+' Months '+
If day(CurrentDate()) < Day({DataTable2.dbirth}) Then
ToText(day(dateserial(year({DataTable2.dbirth}), Month({DataTable2.dbirth})+1,1-1)) - Day({DataTable2.dbirth}) + Day(CurrentDate()),0) + ' days'
Else
ToText(day(CurrentDate()) - day({DataTable2.dbirth}),0) + ' days'

akshay said...

this formula is accurately working for me so far thanks