How to get Sum for my Elapse Time string for my group

[Former Member]

Hi,

How can I get the sum of an elapse time string in my group footer 2 and group footer 1 from the report screenshot attached.

I have a report that display TimeIn and TimeOut of an employee.

For the total time I have created a formula that will take my time in seconds and displaying it in hrs, minutes and seconds.

How can I get the sum of all the total time per day and display this in my group footer 2.

As well I will need to add the grand total to group footer 1 for the total of days.

The formula I have for the total time is as follow:

WhileReadingRecords;

NumberVar TotalSec :=  {TimeLogs.TotalTime};

NumberVar Hours   := Truncate  (Remainder ( TotalSec , 86400) / 3600) ;

NumberVar Minutes := Truncate  (Remainder ( TotalSec , 3600) / 60) ;

NumberVar Seconds := Remainder (TotalSec , 60) ;

Totext ( Hours ,   '00' ) +  ':' +

Totext ( Minutes , '00' ) +  ':' +

Totext ( Seconds , '00' )

--------------------------------------------------------------------------------

This is a seperate question below but related as well to this report.

Another question I have is how can I round up for example 03:30:58 to 03:31:00

And also how can I round down for example 03:26:10 to 03:26:00

Any help would be appreciated.

Thank you,

Joe

[JWiseman · Accepted solution]

hi Jocelyn,

if you put your  {TimeLogs.TotalTime} field on your report, right click on it and choose Insert > Summary and then leave it as type = Sum and then have it inserted on the desired group level.

now right click on the new Summary and choose Format Field > Common tab > Display string and enter this syntax

numbervar ts:= currentfieldvalue;
totext(dateadd("s",ts,currentdate),'HH:mm:ss');

for the second question please post a new discussion as per the forum rules.

-jamie

[Former Member · Accepted solution]

Thank you, that worked perfectly, much easier this way

[Former Member · Accepted solution]

Hi Jamie,

Well I ran into an issue with this. My Daily Total, there is no issue, it is displaying the proper time. But when I add a summary to my Group Footer 1, it doesn't add up properly.

If I select only 1, 2, or 3 days my Date Range Total is adding fine but when selecting 5 days for example, as you can see from the picture below, for an unknowned reason the total of hrs is wrong.

Do you know why this is happening.

Than you for your help.

Joe

Picture below, this is working fine?

[JWiseman · Accepted solution]

hi Joe,

sorry...i should have given you a second Display String option as the one above is good for periods under 24 hours.

for the Display String formula use this instead as it has an output for days as well...

numbervar ts:= currentfieldvalue;

numbervar ds;

numbervar hs;

numbervar ms;

numbervar ss;

ds:= truncate(ts/86400);

hs:= truncate((remainder(ts,86400))/3600);

ms:= truncate((remainder(ts,3600))/60);

ss:= truncate(remainder(ts,60));

stringvar display:= totext(ds,0,"") + ":" + totext(hs,0,"") + ":" + totext(ms,0,"") + ":" + totext(ss,0,"");

display

[Former Member · Accepted solution]

Hi Jimmy,

Thanks for the reply and the help. But I do not wish to display days, I wish to total the amount of hrs, minutes and seconds.

Would this be possible?

From the first picture I attached to the previous message, the total hrs for that date range, 5 days, would total aprox. 39 hrs and some minutes.

Hope this can work.

Again thank you

Joe

[JWiseman · Accepted solution]

sure thing...change the display string formula to this

numbervar ts:= currentfieldvalue;

numbervar hs;

numbervar ms;

numbervar ss;

hs:= truncate(ts/3600);

ms:= truncate((remainder(ts,3600))/60);

ss:= truncate(remainder(ts,60));

stringvar display:= totext(hs,0,"") + ":" + totext(ms,0,"") + ":" + totext(ss,0,"");

display

[Former Member · Accepted solution]

Awesome, that worked great, one last little thing if you don't mind, how can I get back to display the hrs, mins, seconds as double digit.

For example: Right now with the last formula I have this 7:50:9 , how can I get 07:50:09 ?

See picture below for reference.

Again thanks for all the help.

Joe

[JWiseman · Accepted solution]

you can customize the output using the totext function. please see the online help for that function & in particular Format Strings.

numbervar ts:= currentfieldvalue;

numbervar hs;

numbervar ms;

numbervar ss;

hs:= truncate(ts/3600);

ms:= truncate((remainder(ts,3600))/60);

ss:= truncate(remainder(ts,60));

stringvar display:= totext(hs,'00') + ":" + totext(ms,'00') + ":" + totext(ss,'00');

display

[Former Member · Accepted solution]

Again awesome. Its working perfectly now. Your the best Jamie .

All your help is really appreciated.

Thanks

Joe

[Former Member]

Hey Joe,

This is the useful and suit your issue:

c# - How to get sum of time duration of group records in group footer in crystal reports? - Stack Ov...

Thanks,

DJ