Summing Total Time

[gsmithmme]

I am using Crystal 8.5 on Paradox database. I have an out and back field (both datetime) which I am calculating the duration between the two correctly. So, 9:00 am to 5:00 pm is 8 hours and so forth. So it looks like:

5/1/2012 9:00 AM 5/1/2012 5:00 pm 8:00
5/28/2012 8:00 AM 5/29/2012 6:00 pm 34:00
5/1/2012 6:00 AM 5/1/2012 7:30 pm 13:30

I used the date diff (datediff("s", {Crew.Out},{Crew.Back}) to calculate the total number of seconds for each row and then summed it in the footer. However, when I try to convert this to hours:minutes, the number is way off. In other words if I took my calculator to the above example I get 55:30 total hours, but my conversion is way off (in MY data I add up 179:30 on my calculator but the conversion says 280:29 and all none of my records have 29 minutes).

Here is my conversion formula for the total duration (I commented out seconds):

whileprintingrecords;
numberVar dur:=Sum ({@datediff});
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,"00") + ":" + totext(min,"00");// + ":" + totext(sec,"00");
hhmmss

What am I missing here? Thanks.

 

[briangriffin]

There are probably several ways to do this, but I use the mod function to return only the remainder:


whileprintingrecords;
numbervar v_hours;
numbervar v_minutes;
numbervar v_seconds;

v_hours := truncate(Sum ({@Diff})/ 3600);
v_minutes := truncate(({@Diff} mod 3600)/60);
v_seconds := ((Sum({@Diff}) mod 3600) mod 60);

totext(v_hours,0) + " hr. " + totext(v_minutes,0) + " min. " + totext(v_seconds,0) + " sec.

 

[gsmithmme]

Your result gave me the same result (although formatted much nicer!). But the figure still appears off.. A check of the start & end times results in the correct number of seconds, so I don't think the problem is related to the conversion from seconds to hours/minutes, but I am guessing in how it sums the total number of seconds for each record. And I am correct as I summed up the seconds on my calculator and it doesn't match what Crystal is summing.

So.. this ones on me.. I must have something wrong in my grand total.

Thanks for your help. I will use your formatting

 

[briangriffin]

My grand total is 199,805 but I added five seconds to the last end time (7:30:05) so I would have seconds to calculate.

That gives me a total of 55 hours, 30 minutes, and 5 seconds. So I would agree that your total is probably not cooperating.

 

[IanWaterman]

Where have you placed your evaluation of var formula. For Sum ({@datediff});
to work the formula must be placed in report footer. I suspect you have it in a group footer and you are showing total report value instead of group.

If in group change to Sum ({@datediff}, {groupfield]);

Failing that you have duplications. You could change to use a Running total and set evaluate conditions of RT to eliminate duplicated values.

Ian