Convert seconds to HH:MM

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??

LVL 7
osiris247Asked:
Who is Participating?
 
bdreed35Commented:
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
 
peter57rCommented:
Hi martmac,

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

 ('\' not '/')


Pete
0
 
janmariniCommented:
Try Ctime(hr, min, sec) or TimeSerial(hr,min,sec)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
bdreed35Commented:
oops, last line of formula should be this:

formula=cstr(hr,"00") & ":" & cstr(mn,"00") & ":" & cstr(r2,"00")
0
 
osiris247Author Commented:
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
 
peter57rCommented:
Whoops......

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

Pete
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.

All Courses

From novice to tech pro — start learning today.