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.
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!!
ReplyDeleteThanks Terry! I'm pleased it was of use. :-)
DeleteChris
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)]
ReplyDeleteIf 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")
excelent work!!! :)
ReplyDeleteJust wanted to let you know this is still helping people in 2020. Thanks!
ReplyDeleteThat's great to hear!
Delete