How to display age in X Years Y Months Z Days from two dates.

[Former Member]

Hi,

I have been asked sometimes back how can we display the age of a person in for e.g. 20 Years 5 months 10 days format in Webi report. I worked through it and got it working so thought of converting this into Crystal reports as many of us can come across situation where we may be asked to display the data in such a way.

So here are the steps...

Step 1. Create a formula with name BDate where you can directly refer some database date fields or you can insert hard coded date like I used for my testing.

Step 2. Create another formula with name CurrDate where we will capture the current date based on which we will identify the age of an entity.

Step 3. Create a formula with name CurrDatedayYear with definition as

    DateDiff("d",Cdate(Year({@CurrDate}),01,01),{@CurrDate})

    This formula is going to identify the day number for current date in that specific year which we will be using in further formulas.

Step 4. Create another formula BDateDayYear with definition as

    DateDiff("d",Cdate(Year({@BDate}),01,01),{@BDate})

    This formula is going to identify the day number for date of birth in that specific year which we will be using in further formulas.

Step 5. Finally create an formula as PersonAge that we will be using to display the age in said format with definition.

ToText(Year ({@CurrDate}) - Year ({@BDate}) - (If {@CurrDate}<{@BDate} Then 1 Else 0),0)&" Years "+

ToText((((Month({@CurrDate}) - Month({@BDate})) + 12) -(If Day({@CurrDate}) < Day({@BDate}) Then 1 Else 0)) Mod 12,0)&" Months "+

ToText({@CurrDateDayYear} - {@BDateDayYear} + (If  {@CurrDateDayYear}  >= {@BDateDayYear} Then 0 Else (If  {@CurrDateDayYear} - {@CurrDateDayYear}  < {@BDateDayYear} Then {@BDateDayYear} Else {@CurrDateDayYear}- {@CurrDateDayYear}) ),0)+" Days"

Place the formula wherever suggested this will display the data in X Years Y months Z Days format.

I have tried hard to keep all aspects in mind like Leap Year and tested it against multiple dates for results. Still I would encourage the readers to post their views or comments in order to fine tune or correct the above approach if required.

Please Note that you may have to convert the dates if they are in different formats.

Thank you for time in reading the post. Happy Reporting!!!!

regards

Niraj