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.

6 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
  3. Just wanted to let you know this is still helping people in 2020. Thanks!

    ReplyDelete