Calculating the term between two dates 1

[eriktoronto]

I am trying to calculate the term between two dates (MM/DD/YYYY format) using Crystal Reports XI.

Examples:

start date: 01/13/2006
end date: 01/12/2011
I think the result should be: 4 years, 11 months, 29 days

start date: 06/26/2006
end date: 08/31/2011
I think the result should be: 5 years, 2 months, 5 days

I am trying the DateDiff formula, and it allows me to calculate the numbers of years, months, or days between the two dates. For example, for the number of months:

DateDiff("m",{@Start Date},{@End Date}))
would give me the number of months between the dates

But does anyone know of a formula or any suggestions on how I can calculate the term:

ie: x years, y months, z days

I'll keep working on it. Thank you!

 

[fisheromacse]

I have a formula that we use for precise ages (age in years, months & days). It is not real pretty, but does a number of logic tests. I made a few changes to it so, maybe something like this will work for you:


//{@PartYear}
IF month({@EndDate})=month({@StartDate})
THEN
(
IF Day({@EndDate}) < Day({@StartDate})
THEN
((year({@EndDate}))-1) - year({@StartDate})
ELSE
(year({@EndDate})) - year({@StartDate})
)
ELSE
(
IF month({@EndDate}) < month({@StartDate})
THEN
((year({@EndDate}))-1) - year({@StartDate})
ELSE
(IF month({@EndDate}) > month({@StartDate})
THEN
(year({@EndDate})) - year({@StartDate})
ELSE 00000))


//{@PartMonth}
IF (day({@EndDate}) > day({@StartDate}) or day({@EndDate})=day({@StartDate}))
THEN remainder({@PartMonth},12)
ELSE
(
IF day({@EndDate}) < day({@StartDate})
THEN
remainder({@PartMonth},12)-1
ELSE 999
)


//{@PartDays}
numbervar days;
IF month({@StartDate})=month({@EndDate})
AND day({@StartDate})=day({@EndDate})
THEN days := 0
ELSE
(
IF month({{@StartDate})=month({@EndDate})
AND day({@StartDate})>day({@EndDate})
THEN days := day({@StartDate})-day({@EndDate})
ELSE
(
IF month({@StartDate})=month({@EndDate})
AND day({@StartDate})<day({@EndDate})
THEN days := day({@EndDate})-day({@StartDate})
ELSE
(
IF month({@StartDate})>month({@EndDate})
AND day({@StartDate})<day({@EndDate})
THEN days := day({@EndDate})-day({@StartDate})
ELSE
(
IF month({@StartDate})>month({@EndDate})
AND day({@StartDate})>day({@EndDate})
THEN days := day(dateserial(year({@EndDate}),(month({@EndDate})-1)+1,1)-1)-day({@StartDate})+day({@EndDate})
ELSE
(
IF month({@StartDate})<month({@EndDate})
AND day({@StartDate})<day({@EndDate})
THEN days := day({@EndDate})-day({@StartDate})
ELSE
(
IF month({@StartDate})<month({@EndDate})
AND day({@StartDate})>day({@EndDate})
THEN days := day(dateserial(year({@EndDate}),(month({@EndDate})-1)+1,1)-1)-day({@StartDate})+day({@EndDate})
ELSE
999
))))))



//{@TotalForDisplay}
{@PartYear}&" years, "&{@PartMonth}&" months, "&{@PartDays}&" days old"

 

[eriktoronto]

Thank you very much fisheromacse. I will try this solution and get back with my results. Thanks again.


=============================================

p.s. I had tried the following solution this afternoon that was working for many of my records, but some of them were not calculating correctly so looks like my solution was not good enough. I will try your coding now.. thanks

My test solution (that worked some of the time, but not always)

Year:
// find the number of days between the two dates and divide by 365.25 to get the number of years

Truncate( (DateDiff ("d",{@Lease Start Date} ,{@Lease End Date} ))/365.25,0)


Month:
//use the truncated value from the year calculation and multiply by 12 to get the number of months

Truncate((( (DateDiff ("d",{@Lease Start Date} ,{@Lease End Date} ))/365.25) - Truncate( (DateDiff ("d",{@Lease Start Date} ,{@Lease End Date} ))/365.25,0)) * 12,0)

Day:
//use the truncated value from the month calculation and multiply by the number of days in the end date month field (@MonthFactor) ie: Jan = 31 days, Feb = 28 days, etc)

((( (DateDiff ("d",{@Lease Start Date} ,{@Lease End Date} ))/365.25) - Truncate( (DateDiff ("d",{@Lease Start Date} ,{@Lease End Date} ))/365.25,0)) * 12 - Truncate((( (DateDiff ("d",{@Lease Start Date} ,{@Lease End Date} ))/365.25) - Truncate( (DateDiff ("d",{@Lease Start Date} ,{@Lease End Date} ))/365.25,0)) * 12,0)) * {@MonthFactor}


These formulas work for many of the examples. However, for some examples the formalas are off by a bit. For example,

Start Date: 05/01/1995
End Date: 12/31/2010

The above formulas give a result of 15 years, 8 months, 1 day (should be 15 years, 7 months, 30 days). So the above formulas are very close but not quite working.