Tuesday, 16 February 2010

Convert Seconds to Hours, Minutes and Seconds

Sometimes you just have to adjust date formats of objects on the report and not in the Universe. One example I encountered recently was a report where I had to convert the time in seconds into hours, minutes and days on the report as one of the date columns was calculated on the report. I used the following code to achieve the correct conversion:

= If ((IsNull([Total Course Duration (seconds)])) Or([Total Course Duration (seconds)] = 0 )) Then ("00:00:00") Else FormatNumber(Floor([Total Course Duration (seconds)] /3600) ;"00") + ":" + FormatNumber(Floor(Mod([Total Course Duration (seconds)] ;3600)/60) ;"00") + ":" + FormatNumber(Mod(Mod([Total Course Duration (seconds)] ;3600) ;60) ;"00")
 
This code correctly displayed the object in HH:MM:SS format onstead of seconds.

4 comments:

  1. Absolutely Brilliant!! I have searched for HOURS all over the web for a solution to this specific problem of converting seconds into a time and just tried it in BO XIr3 WebI... BINGO. This should also allow me to 'add' times together which was a secondary goal. Done!! Thank you, much!!

    ReplyDelete
    Replies
    1. Thanks Terry! I'm pleased it was of use. :-)
      Chris

      Delete
  2. 2 questions to this answer. I am completely new business objects and am trying to "learn as I go". my current cell reads like this: =[Query 1].[Time Spent (min)]
    If I just needed to change minutes to Hours:Minutes, can I just change the above formula you wrote to read like this?
    = If ((IsNull([[Query 1].[Time Spent (min)])) Or([Query 1].[Time Spent (min)] = 0 )) Then ("00:00:00") Else FormatNumber(Floor([Query 1].[Time Spent (min)] /1440) ;"00") + ":" + FormatNumber(Floor(Mod([Query 1].[Time Spent (min)] ;1440)/60) ;"00")

    ReplyDelete