Solved

# Crystal 9 + create formula to convert seconds to hours/minutes

Posted on 2007-10-15
Medium Priority
4,322 Views
using crystal 9.0...I am trying to do the following.
I have a database field that is a value of seconds.
I need to create a formula field using this number in the format of hours:minutues:seconds
the basic syntax or the crystal syntax will be fine...
I would be interested in seeing both.
I have not created a crystal formula before.

Thanks
0
Question by:Robb Hill
• 4
• 3
• 2
• +1

LVL 77

Expert Comment

ID: 20079350
Create a formula field:

format the result to show just a time.
0

LVL 101

Expert Comment

ID: 20079607
If you just need the time then try

Time({YourSecondsField}/86400)

mlmcc
0

LVL 11

Author Comment

ID: 20079943
its not working....

lets say I have a field called seconds.....and the value is 100000 seconds.
I would like the display to be in this format.

HH:MM:SS

And the real big if here that I cant get to work...is this is value is being summmed in a pivot table.

So after pivot this is what I have before trying to convert

job 1     seconds   total
0

LVL 11

Author Comment

ID: 20079951
oops

job1  seconds   total
job2  seconds   total
job3  seconds   total
grand total

so I need both the row values in seconds to read in the HH:MM:SS format as well as the totals..
and note this is a crosstab
0

LVL 77

Expert Comment

ID: 20080785
'its not working....'

what does that mean?

Based on what you have stated both solutions should work.

0

LVL 11

Author Comment

ID: 20080912
how do I see just time ....

maybe im not typing it in right.....

I cannot just type
Time({YourSecondsField}/86400)

in formula editor
0

LVL 11

Author Comment

ID: 20081026
Specifically I need seconds converted to the number of hours and minutes in those seconds...

for example

60 seconds = 1
121 seconds - 1:0:1
0

LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 20081491
In Crystal time is limited to a maximum of 23:59:59 (1 day)

If you want to display your seconds as hh:mm:ss where hh can be > 24 use this formula

Local NumberVar myHours;
Local NumberVar myMinutes;
Local NumberVar mySeconds;

mySeconds := {YourSecondsField};
myHours := mySeconds \ 3600;
mySeconds := mySeconds mod 3600;
myMinutes := mySeconds \ 60;
mySeconds := mySeconds mod 60;

ToText(myHours,0) & ":" & ToText(myMinutes,0) & ":" & ToText(mySeconds,0)

If you are cocluating or acculmulating the seconds add

Global NumberVar YourSecondsVariable;
mySeconds := YourSecondsVariable;

mlmcc
0

LVL 77

Expert Comment

ID: 20081618
'I cannot just type
Time({YourSecondsField}/86400)'

I think the idea is that you use the name of your seconds field.
0

Expert Comment

ID: 21905853
Hi,

Thanks for the solution - I have tweaked the solution given by mlmcc to make the output look more consistent, the given code above would give you the output 0:8:7 if the seconds were 487. With my change below it would be 00:08:07, so that down the column in the report minutes, hours and seconds would be in the same alignment consistently.

Cheers,

IronmanJames
``````Local NumberVar myHours;
Local NumberVar myMinutes;
Local NumberVar mySeconds;

mySeconds := {YourSecondsField};
myHours := mySeconds \ 3600;
mySeconds := mySeconds mod 3600;
myMinutes := mySeconds \ 60;
mySeconds := mySeconds mod 60;

(IF myHours < 10 THEN ("0" & ToText(myHours,0)) ELSE (ToText(myHours,0)))
& ":" &
(IF myMinutes < 10 THEN ("0" & ToText(myMinutes,0)) ELSE (ToText(myMinutes,0)))
& ":" &
(IF mySeconds < 10 THEN ("0" & ToText(mySeconds,0)) ELSE (ToText(mySeconds,0)))
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerās expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. ā¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyā¦
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery procedā¦
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
###### Suggested Courses
Course of the Month14 days, 11 hours left to enroll