Ash Burton

Mobile | Web | Data

Mobile Payments, Android, iOS, Mobile Web, HTML5, Multichannel Customer Service, Web Services, IVR, Business Intelligence, Telecoms, Radio, Photography, Running, Gaming

Convert Seconds to a Time Format (HH:MM:SS) in Business Objects / Web Intelligence

Every now and again you'll have a value coming from a database in seconds, this is usually very handy since you can perform operations on a nice friendly numeric value but often I find that the user will want to see the outcome in a more familiar time format. I had this problem the other day and rather than write the formula from scratch I used Google and came across Christian Key's solution which mostly solved my problem.  Christian's solution returns a formatted string, however I needed to return a value that Business Objects understood as a time, in order to that all I had to do is wrap his solution in a ToDate() function as follows:

=ToDate(If((IsNull([TestSeconds])) Or([TestSeconds] = 0 )) Then ("00:00:00") 
Else FormatNumber(Floor([TestSeconds] /3600) ;"00") + ":" + 
FormatNumber(Floor(Mod([TestSeconds] ;3600)/60) ;"00") + ":" + 
FormatNumber(Mod(Mod([TestSeconds] ;3600) ;60) ;"00");"HH:mm:ss")

It is best to supply integer seconds into the above formula, feeding decimals can cause unusual rounding errors and provide times like 02:60 instead of 03:00.

Many thanks to Christian and I hope my little addition helps a fellow WebI user out there.