R – How to display a total of time values

crystal-reports

Using Crystal Report 8.5

In Crystal report time column values are String. I display a record order by person id.

Like this.

Personid Intime Outtime

001 08:00:00 17:00:00
001 14:00:00 22:00:00
002 07:00:00 10:00:00
002 04:00:00 21:00:00

…,

From the above report i need a total of Intime, total of Outtime group by personid

Expected Outtput

Personid Intime Outtime

001   08:00:00 17:00:00
001   14:00:00 22:00:00
Total 22:00:00 39:00:00

002   07:00:00 10:00:30
002   04:00:00 21:00:30
Total 11:00:00 31:01:00

…,

I will display all the value along with total by personid wise.

Need Crystal Report Formula Help.

Best Answer

Convert your times to seconds and then get your total. In your display format the seconds as hours, minutes seconds.
For the Intime field, in the Crystal Report, create a formula in your details section: 

@IntimeSeconds  
ToNumber(Left({table.Intime}, 2) * 3600) + ToNumber(Mid({table.Intime}, 4, 2) * 60) + ToNumber(Mid({table.Intime}, 7, 2))

You can now place a formula in the group footer to display the total Intime seconds as hh:mm:ss 

@ShowTotalIntime  
Numbervar TotalTime := Count({table.Intime}, {table.Group});
Numbervar IntimeHours := TotalTime\3600;  
NumberVar IntimeMinutes := (TotalTime%3600)\60;  
Numbervar IntimeSeconds := (TotalTime%60);  
ToText(IntimeHours, "00") + ":" + ToText(IntimeMinutes, "00") + ":" ToText(IntimeSeconds, "00");

Do a similar thing for Outtime.

Related Topic