Running Total for Time Field

[Nitesh]

Hi Guys,

I have a field called Total Trip Time in my table. It stores a value in the format hh:nn:ss as time. The field type is Time. How can I calculate a grand total that adds each time value in the recordset in my Crystal Report.

Please help.

[brucevde]

Create a formula that converts the time values into seconds. Sum the formula and then convert this Grand Total into hh:mm:ss. If the Grand Total can possibly go over 24 hours then you can't use any of the normal date/time functions but convert using simple math.

[Nitesh]

Create a formula that converts the time values into seconds. Sum the formula and then convert this Grand Total into hh:mm:ss. If the Grand Total can possibly go over 24 hours then you can't use any of the normal date/time functions but convert using simple math.

Hi Brucevde,

Thanks alot for replying. Can you please give me an example of the formula to use. I have no idea how formulas work in Crystal. I am still new to Crystal.

[brucevde]

I have no idea how formulas work in Crystal.

That is why they invented help files.

Basically, a formula is executed/calculated for every record in the data set. Converting a date field to seconds you could write something like

(Hour({TableName.FieldName}) * 3600) +
(Minute({TableName.FieldName}) * 60) +
Second({TableName.FieldName})

The results of the formula can then be printed/formatted on the report just like a database field or used in other calculations, like summaries.

[Nitesh]

Thanks Brucevde,

I will try it out today.

[Nitesh]

Hi Brucevde,

Please help me out. I have a field in my db called TotTripTime stored as a Time Data type. In my crystal report query i specify that IF(t.TotTripTime = '00:00:00','',t.TotTripTime).

Problem is, the date field is now a string in the report because of the way I have written the query as above.

So what I tried was creating 2 different functions:

1. One to convert the String Time to an actual time:

Time({Command.TotTripTime})

2. And then another function to calculate the total based on the above functions value:

(Hour({@DriverHour}) * 3600) +
(Minute({@DriverHour}) * 60) +
Second({@DriverHour})

But when I run my report it displays absolutely nothing. Please, please help me. It's almost working.

[Nitesh]

Ok Brucvde,

I've coem up with these 3 functions:

1: CovertTimeStringToSeconds

local numbervar hours;
local numbervar minutes;
local numbervar seconds;

// Parse out the hours portion of the string and
// multiply by 3600 to convert to seconds
hours := tonumber({Command.TotTripTime}[1 to 2])* 3600;

// Parse out the minutes portion of the string and
// multiply by 60 to convert to seconds
minutes := tonumber({Command.TotTripTime}[4 to 5]) * 60;

// Parse out the seconds
seconds := tonumber({Command.TotTripTime}[7 to 8]);

// Add up all the seconds
hours + minutes + seconds;

2: TotalSeconds:

 sum({@ConvertTimeStringToSeconds})

3: CovertTotalSeconds:

local numbervar RemainingSeconds;
local numbervar Hours ;
local numbervar Minutes;
local numbervar Seconds;

//divide the @TotalSeconds by 3600 to calculate
// hours. Use truncate to remove the decimal portion.
Hours := truncate({@TotalSeconds} / 3600);


// Subtract the hours portion to get RemainingSeconds
RemainingSeconds := {@TotalSeconds} - (Hours *
3600);

// Divide RemainingSeconds by 60 to get minutes.
// Use truncate to remove the decimal portion.
Minutes := truncate(RemainingSeconds/60);

// Subtract the Hours and Minutes and what is left
// over is seconds.
Seconds := {@Totalseconds} - (Hours * 3600) -
(Minutes * 60);


// Format the hours, minutes, and seconds to hh:mm:ss
totext(Hours,"00") + ":" + totext(Minutes,"00") +
":" + totext(Seconds,"00")

But when I run the report I get a massage sayin "the string is non-numeric"

Please help