• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4329
  • Last Modified:

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

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
Robb Hill
Asked:
Robb Hill
  • 4
  • 3
  • 2
  • +1
1 Solution
 
peter57rCommented:
Create a formula field:

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

format the result to show just a time.
0
 
mlmccCommented:
If you just need the time then try

Time({YourSecondsField}/86400)

mlmcc
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
Robb HillSenior .Net DeveloperAuthor Commented:
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
 
peter57rCommented:
'its not working....'

what does that mean?

Based on what you have stated both solutions should work.

0
 
Robb HillSenior .Net DeveloperAuthor Commented:
how do I see just time ....

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

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

in formula editor
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
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
 
mlmccCommented:
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
 
peter57rCommented:
'I cannot just type
Time({YourSecondsField}/86400)'

I think the idea is that you use the name of your seconds field.
0
 
IronmanJamesCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now