Kiran's personal website
Blog    Forums   Sitemap   Social    Phone

Knowledge base home, and site home.
List of all Knowledge base categories.
An alphabetical list of technical terms.
Contact Me
Contact me for knowledge base questions
My Blog
My articles, white papers, ideas and thoughts.


Converting time format from seconds to HH:MM:SS in Crystal Reports

Date Added January 1, 1970 | Print | Bookmark
'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
'(You can concise the following code using formats)

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 (you can also use string formats)
str=CStr (hr)
str1=CStr (mn)
str2=CStr (r2)

'Get rid of the decimal values (you can also use string formats)
str = left(str, len(str)-3)
str1 = left(str1, len(str1)-3)
str2 = left(str2, len(str2)-3)

'format in to "00" (you can also use string formats)
if len(str)<=1 then
    str="0" & str
   str= str
end if

if len(str1)<=1 then
  str1="0" & str1
  str1= str1
end if

if len(str2)<=1 then
  str2="0" & str2
  str2= str2
end if

'Finally concatinate and add it to formula
formula= str & ":" & str1 & ":" & str2

Was this article helpful?

Yes No

Category: Crystal Reports - General

Last updated on November 22, 2009 with 33576 views

17 User Comments

Fted Flintstone
January 1, 1970 12:33 am

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)

January 1, 1970 12:33 am


I did everything you said
but the only thing I see is "00".

Is this working of you put the formatted field into the
details section so the formula effects every row of the
I\'m searching for a solution for a whil now en nothing
seems to work fine

I already made a method that worked but it always keeps
showing the decimals.

Pleas can you help me
I would be very happy if this problem is finally solved

January 1, 1970 12:33 am

found this on the internet...


NumberVar TotalSec := {YourTable.TotalSeconds};
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;

Totext ( Days , \'##\' ) + \':\' +
Totext ( Hours , \'00\' ) + \':\' +
Totext ( Minutes , \'00\' ) + \':\' +
Totext ( Seconds , \'00\' )

If you want to display only in hours and minutes you should use this version:

NumberVar TotalSec := {YourTable.TotalSeconds};

NumberVar Hours := Truncate ( TotalSec / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);

Totext ( Hours, \'####\') + \':\'+
Totext ( Minutes,\'00\')

Cyndi Baker
January 1, 1970 12:33 am

In Crystal Syntax:

totext(abs({database.field} / 3600),"00") & ":" &
totext(abs(remainder({database.field},3600) / 60),"00") & ":" &

Tom D
December 14, 2009 10:10 pm


I have two words for you..


Your Crystal formula worked like a charm.

December 18, 2009 05:06 pm

Bookmarked and thank you Cyndi!!!!!!!!!

Cyndi Baker
January 27, 2010 07:33 pm

You're welcome!

Randy W
February 2, 2010 06:22 pm

I needed to modify Cyndi's slightly or I got errors with values shorter than 1 hour but more than 30 minutes, so changed it to this:

totext (floor(abs({databasefield} / 3600)),"00") & ":" &
totext(abs(remainder({databasefield},3600) / 60),"00") & ":" &

January 6, 2011 06:14 am

thanks !! it worked

October 27, 2017 01:47 pm

Самые эффективные заменители Виагры Разные проблемы с потенцией в старшем возрасте может испытывать огромное число мужиков, но современная медицина очень хорошо борется с указанной проблемой с помощью Виагры. Виагра по сути своей — классный препарат с очень небольшим количеством побочек, но ее ощутимым минусом считается высокая рыночная стоимость. Ниже мы поговорим о некоторых, наиболее действенных заменителях дорогой Виагры:

Сиалис — это самый старый аналог классического лекарства для усиления эррекции на отечественном рынке. Заказать указанное лекарство а также дженерик сиалис как действует по наиболее низкой цене вы можете на сайте

Используется и дозируется качественный сиалис точно так же, как и простая Виагра.

Почти все знают, что после Виагры было придумано и изготовлено большое количество препаратов с синергичным действием на организм. Наиболее сложным препаратом такого рода является Левитра, которая действует на эррекцию намного быстрее, при этом меньше воздействуя на сердечно-сосудистую систему мужчины. Западные доктора, по результатам последних опросов, начинают все чаще прописывать Левитру вместо простой Виагры.

Шпанская мушка стала лучшим природным афродизиаком со сходным с Виагрой действием, который подойдет и женщинам и мужчинам. Привлекательной стороной экстракта шпанской мушки является минимум побочных эффектов, но стоит он чуть больше чем другие лекарства. Все описанные в материале дженерики вы сумеете приобрести через интернет и выбрать доставку почтой. Посылка будет на на 100 процентов анонимной, данные клиента удаляются сразу после осуществления доставки.

November 12, 2017 06:06 pm

Priligy Serve La Ricetta Medica Amoxilcillin Compare To Amoxil Clav Vardenafil Generic Levitra viagra cialis Soft Sidenifil Sell Acyclovir 800 Walmart Generic Drug List

November 21, 2017 05:45 pm

Discount Levitra No Rx Purchase Generic Plavix Lasix Uk cialis Cialis 20g Propecia En Ligne France Propecia Merck 1 Mg

May 4, 2018 05:34 pm

Cialis Kamagra Sildenafil cialis 5mg Amoxicillin Suspension Kids Taste Viagra Cialis For Sale
June 5, 2019 04:52 am

Hello There. I found your weblog using msn. That is an extremely smartly written article. I will make sure to bookmark it and return to learn more of your helpful info. Thank you for the post. I'll definitely return.

готовые домашние задания английскому
January 24, 2020 06:43 pm

Superb blog you have here but I was curious if you knew of any discussion boards that cover the same topics discussed in this article? I'd really love to be a part of online community where I can get comments from other experienced people that share the same interest. If you have any suggestions, please let me know. Appreciate it!| а

February 12, 2020 01:30 am

Amoxicilline Acide Clavulanique Generique - buy cialis online Nuit Cialis Buy Cialis Cialis Donde Comprar Espana

March 6, 2020 07:31 pm

how cocos it would to canucks what are the best essay writing services and the gassy harness is precipice the pyelonephritis longest

Leave a Comment

Security code


Home | About Me | Products | Support | Contact | Knowledgebase | Forums | Blog | Site map | Site Search | Subscribe | Links | Social | News

Travel | Guest Book | All Pages | History | Site Calendar | Recipes | Ex-Rates | UK Stuff | Downloads | Telugu Stuff | Tell a Friend | Feedback

Bookmark and Share
Terms of Use
Site contents Copyright © 2008-2009 Kiran Reddy. All rights reserved.