Skip to Content
0
Sep 22 at 11:03 PM

Grand Total for Total time

87 Views Last edit 6 days ago 2 rev

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?