Crystal Reports Date Difference

>

I have searched and searched over the internet many times trying to perfomr the simple task fo calculating the difference between two dates in Crystal Reports. The result I was looking for had to be formatted in DD:HH:MM:SS. After hours of searching here is how I did it.

Create a formula field that gets the total number of seconds between the start date and completed date:

DATEDIFF(“s”, {Incidents.Start_Date},{Incidents.Completed} )

I then used a second formula field to perform the arithmetic to calculate the remainders and format the output:

WhilePrintingRecords;
NumberVar TotalSec := {@TotalSeconds};
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 , ‘##’ ) + ‘:’ +
Totext ( Hours , ’00’ ) + ‘:’ +
Totext ( Minutes , ’00’ ) + ‘:’ +
Totext ( Seconds , ’00’ )

There that works!! It seemed like it took forever to find the answer to this problem!
Up Next is how to do this based on a work calendar!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: