HALP! I need to sum time fields 1

[jbeary]

HELP ME PLEASE, I'm about to blow my brains out.

I have tried everything to sum time formula fields formatted in H:MM:SS. Is it possible to add something like 0:04:25 to 0:36:36 to result in 0:41:01?

THANKS!

 

[synapsevampire]

I'll assume that it is a type time field, and that you have a reasonably recent version of Crystal.

In the details add this formula:
whileprintingrecords;
stringvar secs:= secs+
(val(left({table.field},2))/3600)
+
(val(mid({table.field},4,2))/60)
+
val(mid({table.field},7,2));

Now at the end of the report you could use the formula in my FAQ to create the HH:MM:SS display:

faq767-3543

-k

 

[jbeary]

Awesome...

Thanks for bringing me back from the edge. I've yet to try your formula in the report but I'm sure your soluton will work. I never thought of converting all to seconds then converting back to hh:mm:ss... I found another problem with this abortion that the vendor will have to decide on before I go ahead... So I will post again when your solution is implemented.

THANK YOU, THANK YOU, THANK YOU!

 

[jbeary]

Mr. Vampire,

I'm having trouble converting the results back into a time format. Excuse me, but I'm hoping that you can give me some direction as I'm borrowing heavily from your work. Please take a look at the following:

FORMULA #1 (totaling the time diffeences of individual records in a group)

Shared DateTimeVar PID7E;
Shared DateTimeVar PID16S;

numberVar dur := datediff("s",PID7E, PID16S); //get the seconds between 2 dates
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

If PID7E <> #01/01/1900# and PID16S <> #01/01/1900# and PID7E < PID16S then


hhmmss

else

totext("0") + ":" + totext("00") + ":" + totext("00");


WORKS GREAT!

FORMULA #2 (a group field that sums the above formula)

whileprintingrecords;
numbervar secs:= secs + (val(left({@XOpID7-16},2))/3600)
+
(val(mid({@XOpID7-16},4,2))/60)
+
val(mid({@XOpID7-16},7,2));

numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(secs/60)/60);
min := Remainder(Truncate(secs/60),60);
sec := Remainder(secs,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss

This formula does work, but it rolls all the time into one number. Shouldn't I be breaking out the Hrs, Mins, & Secs before doing the division?

Any help would be appreciated!

THANK YOU!

 

[jbeary]

Okay, okay... I'm being silly.. I discoverd my problem... thanks again for all your help... I got it working now...