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.