Date Difference Problems 1

[neilmcdonald]

Hi,

Is it possible to show the difference between two date fields in the format "x years, y months, z days"?

I've searched the forums, but can only find formulas to give days and hours.

I'm using crystal 10.

Any advice would be greatly appreciated.

Thanks,

Neil

 

[Madawc]

You could get the number of days and then use division and remainder to turn it into years, months and additional days.

Or you could find the number of whole years, and then derive a date with that many years subtracted. Then get the difference in months for that date, subtract that many months and finally get the days. Long-winded, but it should work.

Madawc Williams (East Anglia, UK)

 

[neilmcdonald]

Thanks very much - I'll give that a try.

 

[Madawc]

If you get it working, please post it. It's the sort of thing lots of people might want.

Madawc Williams (East Anglia, UK)

 

[bdreed35]

Here is a formula that I use. It is an adaptation of Ken Hamady's Age formula:

datetimevar date1 := {table.date};
datetimevar date2 := currentdate;
numbervar years;
numbervar months;

if (Month(date2) * 100) + Day (date2) >=(Month(date1) *100) + Day (date1) then 
    years := Year (date2) - Year(date1)  
else 
    years := Year (date2) - Year(date1) -1;

date1 := dateadd("yyyy",years,date1);

if Day (date2) >= Day (date1) then 
    months := datediff("m",date1,date2)  
else 
    months := datediff("m",date1,date2) -1;

date1 := dateadd("m",months,date1);

totext(years,"#") & " year(s), " & totext(months,"#") & " month(s), " & totext(datediff("d",date1,date2),"#") & " day(s)"

~Brian