How to show number of days:hours:min:second

[Former Member]

Hi !

Need your help on this one:

I have to calculate the number of ( Days;Hours:Minute:Second ) that a ticket have been opened

But I have a problem to display the format that I want: DD:HH:MM:SS

EX:the ticket have been opened 2011-05-03 11:00:00

first I create a formula to show number of second to show how long the ticket have been opened since the currentDate in seconds

Formula Name is:

{@number of seconds OpeningTime}

Crystal Syntax:

DateTimeVar dt1:= {@DTOpenDate};

DateTimeVar dt2:= {@CurrentDateTime};

If dt2 >= dt1 Then

(

NumberVar ds:= (Date(dt2) - Date(dt1))*86400;

NumberVar hs:= (Hour(dt2) - Hour(dt1))*3600;

NumberVar ms:= (Minute(dt2) - Minute(dt1))*60;

Numbervar ss:= Second(dt2) - Second(dt1);

NumberVar ts:= dshsms+ss)

Else

(

NumberVar ds:= (Date(dt2) - Date(dt1))*86400;

NumberVar hs:= (Hour(dt2) - Hour(dt1))*3600;

NumberVar ms:= (minute(dt2) - Minute(dt1))*60;

NumberVar ss:= Second(dt2) - Second(dt1);

NumberVar ts:= dshsms+ss)

then I create another formula to display the format that I want:Ex:if the ticket have been opened for about 25hh:00mm:00ss

i want to show 01:01:00:00

DD:HH:MM:SS

Formula name: {@DTConversionOpeningTime}

I use Crystal Syntax:

WhilePrintingRecords;

StringVar Days1;

StringVar Hours1;

StringVar Minutes1;

StringVar Seconds1;

NumberVar Duration:=0;

Duration:= {@number of seconds OpeningTime};

If Duration < 0 Then

"Cannot have a time less than zero"

Else

(Days1:=ToText(Truncate(Duration/86400),0);

Hours1:=ToText(Truncate(Duration/3600),0);

Minutes1:=ToText(Truncate(Remainder(Duration,3600)/60),0);

Seconds1:=ToText(Remainder(Remainder(Duration,3600),60),0));

//Display format

(if length(Hours1) < 2 then '0')+ Days1 + ":" +

["0",""][length(Hours1)]+ Hours1 + ":" +

["0",""][length(Minutes1)] + Minutes1 + ":" +

["0",""][length(Seconds1)] + Seconds1;

Thanks for your help

[Former Member]

Find a solution, see thread:

Re: Converting a calculated field into format of dd:hh:mm:ss answered by Jason Long