That seems a bit long winded - try this:-
Dim totSecs As Number
Dim hh As Number
Dim mm As Number
Dim ss As Number
Dim mmss As Number
totSecs = your_field_here
hh = int(totSecs \ 3600)
mmss = totSecs Mod 3600
mm = int(mmss \ 60)
ss = mmss mod 60
formula = Right("00" & Trim(cstr(int(hh), "##")), 2) & ":" & _
Right("00" & Trim(cstr(int(mm), "##")), 2) & ":" & _
Right("00" & Trim(cstr(int(ss), "##")), 2)
Converting time format from seconds to HH:MM:SS in Crystal Reports
'How can I convert time format from seconds to HH:MM:SS in Crystal Reports?(Basic Syntax)
‘Code by Kiran Reddy
'Change time format from seconds to HH:MM:SS (Basic Syntax)
'In most of the databases, time is usually saved in seconds as a string or a number
‘You may need to convert the seconds field in to a proper time format
‘Create a formula called "Formatted Time"
‘In the formula editor select basic syntax, add following code and replace {YOUR FIELD} with your database field.
‘Save and keep your formula on the report
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
hr=int({YOUR FIELD}\ 3600) 'get hours
r1=Remainder ({YOUR FIELD},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)
'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 concatinate and add it to formula
formula= str & ":" & str1 & ":" & str2
Category: Crystal Reports - General,