[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert seconds to HH:MM

Posted on 2006-04-28
6
Medium Priority
?
990 Views
Last Modified: 2010-08-05
Hi

I have a field i am getting from the database 'Seconds' as an int.

How in my crystal formula can i convert the int to the format HH:MM ?

It seems simple but this is not working....

Dim hr As Number 'to hold hrs part
Dim mn As Number 'to hold minutes
Dim sc As Number 'to hold seconds
Dim r1 As Number 'to hold reminders
Dim r2 As Number 'to hold reminders
Dim str As String 'to hold string hrs
Dim str1 As String 'to hold string minutes
Dim str2 As String 'to hold string seconds
Dim start as number

start = int({Summary.intInCourse}/{Summary.intUserTotal})
hr=start/ 3600 'get hours
r1=Remainder (start,3600 ) 'rest of seconds after hours taken out
mn=int(r1 / 60) 'get minutes
r2=Remainder (r1,60 ) 'rest of the seconds

'Convert all values in to strings to format in to "00" and to remove decimals
str=CStr (hr)
str1=CStr (mn)
str2=CStr (r2)

'to get rid off the decimal values
str = left(str, len(str)-3)
str1 = left(str1, len(str1)-3)
str2 = left(str2, len(str2)-3)

'format in to "00"
if len(str)<=1 then
str="0" & str
else
str= str
end if
if len(str1)<=1 then
str1="0" & str1
else
str1= str1
end if
if len(str2)<=1 then
str2="0" & str2
else
str2 = str2
end if
'Finally add to formula
formula= str & ":" & str1 & ":" & str2

any ideas??

0
Comment
Question by:osiris247
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 16562420
Hi martmac,

cstr(numSecs\3600) & ":" & cstr((numsecs mod 3600)\60

 ('\' not '/')


Pete
0
 
LVL 7

Expert Comment

by:janmarini
ID: 16562455
Try Ctime(hr, min, sec) or TimeSerial(hr,min,sec)
0
 
LVL 28

Accepted Solution

by:
bdreed35 earned 2000 total points
ID: 16562517
You can simplify your formula down to this:

Dim hr As Number 'to hold hrs part
Dim mn As Number 'to hold minutes
Dim r1 As Number 'to hold reminders
Dim r2 As Number 'to hold reminders

Dim start as number

start = int({Summary.intInCourse}/{Summary.intUserTotal})
hr=start/3600 'get hours
r1=Remainder(start,3600) 'rest of seconds after hours taken out
mn=int(r1 / 60) 'get minutes
r2=Remainder (r1,60 ) 'rest of the seconds

formula=cstr(hr,"00") & ":" & cstr(mn,"00") & cstr(r2,"00")
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
LVL 28

Expert Comment

by:bdreed35
ID: 16562521
oops, last line of formula should be this:

formula=cstr(hr,"00") & ":" & cstr(mn,"00") & ":" & cstr(r2,"00")
0
 
LVL 7

Author Comment

by:osiris247
ID: 16562680
thanks peter nearly there....just need to strip the decimal place off.

hr = round((start\3600),0)
mn = round((start mod 3600),0)\60

seems has no affect??
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16562757
Whoops......

cstr(numSecs\3600,"0") & ":" & cstr((numsecs mod 3600)\60,"00" )

Pete
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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