?
Solved

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

Posted on 2007-10-15
10
Medium Priority
?
4,322 Views
Last Modified: 2009-07-16
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
please help...
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
Comment
Question by:Robb Hill
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20079350
Create a formula field:

Dateadd("s",{table.secondsfield},datetimevalue(0))

format the result to show just a time.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 20079607
If you just need the time then try

Time({YourSecondsField}/86400)

mlmcc
0
 
LVL 11

Author Comment

by:Robb Hill
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
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
LVL 11

Author Comment

by:Robb Hill
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

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

what does that mean?

Based on what you have stated both solutions should work.

0
 
LVL 11

Author Comment

by:Robb Hill
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

by:Robb Hill
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

by:
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

by:peter57r
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

by:IronmanJames
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)))

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question