Grand Total for Total time

[putertech]

Hello Crystal Community. I am trying to figure out the Grand Total of my Total Time Spent field in HH:MM:SS. This is my data

Date Time ID# Unit Start Time End Time Total Time Spent(@Start to End Total HH:MM:SS)

09/04/22 0:42:33 14529 A 0:42.37 0:49.47 0:07:10

09/04/22 8:13:21 14530 A 8:14.21 8:33.58 0:19:37

09/04/22 13:05:57 14531 A 13:06.19 13:28.03 0:21:44

09/04/22 15:27:19 14532 A 15:27.50 15:38.59 0:11:09

09/04/22 17:46:58 14533 A 17:47.26 17:58.41 0:11:15

09/04/22 19:54:53 14534 A 19:55.46 0:02:02

09/04/22 20:41:59 14535 A 20:42.19 21:00.08 0:17:49

09/04/22 22:21:45 14536 A 22:21.57 22:41.04 0:19:07

09/05/22 2:46:37 14537 A 2:46.45 2:54.29 0:07:44

09/05/22 11:59:40 14538 A 11:59.56 12:15.41 0:15:45

09/05/22 20:57:33 14539 A 20:58.01 21:07.56 0:09:55

09/05/22 21:14:50 14540 A 21:15.09 21:32.17 0:17:08

09/05/22 21:59:16 14541 A 21:59.25 22:20.54 0:21:29

TOTAL TIME: 3:01:54

To get the Total Time Spent, I create a few formulas:

@Start to End in Seconds

Time({Report_.EndTime}) - Time({Report_.StartTime})

@Start to End in HH:MM:SS

CStr(int({@Start to End in Seconds} / 86400), "0") + ":" + CStr(CTime({@Start to End in Seconds} / 86400), "mm:ss")

I now need a Total of the Grand Total Time Spent. I created two formulas:

@Convert HH:MM:SS to seconds

local stringvar array completetime;

local numbervar totalseconds;

completetime:=split({@Start to End in HH:MM:SS},":");

totalseconds:= (3600*cdbl(completetime[1])) + (60*cdbl(completetime[2]))+(cdbl(completetime[3]));

@Convert to HH:MM:SS

replace(cstr(floor(Sum ({@Start to End in Seconds})/3600)),".00","")

+ ":" + replace(cstr(floor((Sum ({@Start to End in Seconds}) mod 3600)/60)),".00","")

+ ":" + replace(cstr(floor(Sum ({@Start to End in Seconds}) mod 60)),".00","")

This gives me the total of 3:1:54 instead of 03:01:54. How do I fix my formula so I get the correct calculation?

[DellSC]

Try this for your last formula:

Right("0" + CStr(Sum ({@Start to End in Seconds})\3600), 0, ""), 2)

+ ":" + Right("0" + CStr(Sum ({@Start to End in Seconds}) mod 3600)\60),0,""), 2)

+ ":" + Right("0" + CStr(Sum ({@Start to End in Seconds}) mod 60),0,""), 2)

I replaced your forward slashes with back-slashes - which are the operator for "Integer Divide". This gives just the integer part of a division operation. I also used the parameters for CStr that remove decimal places during the conversion to string. Finally, this appends a 0 to the left of each number and then takes the two characters on the right, which will add the 0 to the front of single digit numbers.

-Dell

[putertech]

Hi Dell,

Thank you for your response. When I put this formula in, I get "The remaining text does not appear to be part of the formula". And it highlights this:

, 2)

+ ":" + Right("0" + CStr(Sum ({@Start to End in Seconds}) mod 60),0,""), 2)

[DellSC]

Try replacing + with &, which is the VB operator for concatenating strings.

-Dell

[putertech]

I changed it to this and still get same error:

Right("0" & CStr(Sum ({@DP to CL in Seconds})\3600), 0, ""), 2)

& ":" & Right("0" & CStr(Sum ({@DP to CL in Seconds}) mod 3600)\60),0,""), 2)

& ":" & Right("0" & CStr(Sum ({@DP to CL in Seconds}) mod 60),0,""), 2)

[DellSC]

<facepalm> I missed a couple of parentheses. Try this: (Two "(" after "CStr")

Right("0" & CStr((Sum ({@DP to CL in Seconds})\3600), 0, ""), 2)

& ":" & Right("0" & CStr((Sum ({@DP to CL in Seconds}) mod 3600)\60),0,""), 2)

& ":" & Right("0" & CStr((Sum ({@DP to CL in Seconds}) mod 60),0,""), 2)

-Dell

[putertech]

Hi Dell,

Thank you for your response. We are almost there. Now I get the same error at the last line. What is missing in this last line?

, 2)

& ":" & Right("0" & CStr((Sum ({@DP to CL in Seconds}) mod 60),0,""), 2)

[DellSC]

I think I've got it now. Try this:

Local NumberVar hrs := Sum({@Start to End in Seconds})\3600;
Local NumberVar mins := (Sum({@Start to End in Seconds}) mod 3600)\60;
Local NumberVar secs := (Sum({@Start to End in Seconds}) mod 3600) mod 60;
Right("00" & CStr(hrs, 0, ""), 2) & ":" & 
Right("00" & CStr(mins,0,""), 2) & ":" & 
Right("00" & CStr(secs,0,""), 2)

-Dell

[putertech]

That worked. Thank you very much!!