Formatting elapsed time values

[baldr1c]

I am using CR 8.5 and have a elapsed time field in my DB which is formatted to text. I have to add the time values and then display them as hh:mm:ss.
I have converted the time field to seconds and I can then sum/average etc which is great. I am having problems in converting the seconds value back to the hh:mm:ss format. My method comes adrift when a result is 1:04:02. My formula shows 1:4:2 which is not how I want to see it.

I am using the following formulas;

@Duration in Seconds
if isnull({DURATION}) then 0
else
if
Length ({DURATION}) <= 5
then
tonumber(left({DURATION},instr({DURATION},&quot;:&quot-1)) * 60 +
tonumber(right({DURATION},2))
else
(tonumber(left({DURATION},instr({DURATION},&quot;:&quot-1)))*3600 +
(tonumber(mid({DURATION},instr({DURATION},&quot;:&quot+1,2))) *60 +
tonumber(right({DURATION},2))

The Duration field comes through as;
1:04:05 1 Hr, 4 Mins and 5 secs
4:12 4 Mins and 12 secs

and then to get back to hh:mm:ss

@Duration Display
totext(truncate((Sum ({@Duration Seconds}, {BRB_HIER_COSTCENTRE_T.COSTCENTRE_ID})/3600)),0,&quot;&quot & &quot;:&quot; &
totext(truncate(Remainder(Sum ({@Duration Seconds}, {BRB_HIER_COSTCENTRE_T.COSTCENTRE_ID}),3600)/60),0,&quot;&quot & &quot;:&quot; &
totext(truncate(Remainder(Sum ({@Duration Seconds}, {BRB_HIER_COSTCENTRE_T.COSTCENTRE_ID}),3600/60)),0,&quot;&quot

 

[synapsevampire]

You did all the hard work, just add in &quot;00&quot; (with the quotes, not just 0) on your totext mask.

Example:

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, &quot;0&quot + &quot;:&quot; + totext(min, &quot;00&quot + &quot;:&quot; + totext(sec, &quot;00&quot;
&quot;

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[baldr1c]

Thanks for that it has solved the problem very nicely.

 

[synapsevampire]

No worries, that's what we're paid for.

Er, wait a mintue...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com