Tips.gif (2048 bytes)

 

 

 

 

 

Crystal Reports-Add up Time

To add up or accumulate time can be tricky in any data base program. In this example, the table is named TEMP_TIME. The field that we're going to add up is called DURATION. It's formatted as Date Time in either SQL server or Access.

Each record contains values like "06/15/2001 00:15 AM. The 00:15 is the part we're going to get, as in a 15-minute call.

Make three formulas in Crystal: (upper or lower case doesn't matter)

@HOURSField=Hour({TEMP_TIME.DURATION})

@MINUTESField=Minute({TEMP_TIME.DURATION})

@SECONDSField=Second({TEMP_TIME.DURATION})

Hide these fields in your report. Insert the DURATION field in the detail section and don't hide it. We want that to display, and underneath it we'll add up the values.

Make a grouping on the DURATION field

Sum the values of each of these formulas in the DURATION group section, but put them out of the way (like, far to the right) and hide them.

Make three more fields in the grouping section. Two of these are also going to be hidden


@SubHours=
Sum ({@hoursfield}, {TEMP_TIME.DURATION})+
truncate(Sum ({@minutesfield}, {TEMP_TIME.DURATION})/60)+
remainder({@SubMinSec},60)/100

@subMinSec=
Sum ({@MinutesField}, {TEMP_TIME.DURATION})+
truncate(Sum ({@SecondsField}, {TEMP_TIME.DURATION})/60)+
remainder(Sum ({@SecondsField}, {TEMP_TIME.DURATION}),60)/100

@SumTime=
right(totext({@subhours}/100),2)+":"+
right(totext({@SubHours}),2)+":"
+right(totext({@SubMinSec}),2)

The @SumTime field is the only one you need to display, and should be just below the DURATION field of the detail section. The results will look like this:

03:06:10, which is 3 hours, 6 minutes and 10 seconds

Mini Solve Home