Date Difference between first and last records

[Belmancat]

Hi,

I am trying to calculate the difference between a datetime field in the first detail line and the last detail line per group.

Example. - There are ten detail records per group and each detail line has an enteredDate field. I want to calculate the difference between the entered date on the first detail line and the last detail line and display that difference on the group footer.

Any help would be greatly appreciated

Belmancat

 

[rosemaryl]

Dear Belmancat:

Here is an example in days:

datediff("d",minimum({Table.Date},{Table.GroupedbyField}),maximum({Table.Date},{Table.GroupedbyField}) )


Hope that helps,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Belmancat]

Thanks Rosemary.

That worked great, The only problem is that it rounds to the nearest hour. The dateDiff function only seems to allow one interval type. I am using hours but would like to narrow it down to hours and minutes.

Is there a way to specify hours and minutes instead of just hours?

Thanks in advance

Belmancat

 

[rosemaryl]

Dear Belmancat,

The answer is to use "s" which represents seconds and then transform the result in seconds into d:hh:mm:ss, if you wish.

Here is a simple formula that transforms seconds into Days:Hours:Mins:Seconds

Convert Total Seconds to D H M S

WhilePrintingRecords;
NumberVar TotalSec := datediff("S",minimum({Table.Date},{Table.GroupedbyField}),maximum({Table.Date},{Table.GroupedbyField}) )
;
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, '00', 0,'') + ':'+
Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'') + ':'+
Totext ( Seconds,'00', 0,'')

Hope you find that helpful,
ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Belmancat]

Rosemary.

That worked perfectly.

Thanks for all your help.

Belmancat