Interval Formats for Time

[system]

Hi. I have a number of measures in seconds. Id like to format them as interval time - some software has interval time formats. They would loook like “HH:MI:SS” for hours, minutes and seconds.

How can I do this in business objects webi? Would I need to build it out by parsing out hours, the remaining minutes, then remaining seconds and built a string with the results? Or is there a format mask?

---

twebber (BOB member since 2009-03-23)

[system]

I have an attribute called [T Login] - its a number in seconds. Here’s what I have come up with for interval formating. Its awful but works. Hoping someone else has a better way to do this.

=FormatNumber(Truncate([T Login]/3600;0);"##") + “:” + FormatNumber(Truncate(Mod([T Login];3600)/60;0);"##") + “:” + FormatNumber([T Login] - (Truncate([T Login]/3600;0)*3600) - (Truncate(Mod([T Login];3600)/60;0)*60);"##")

---

twebber (BOB member since 2009-03-23)

[system]

Hi,

@twebber

Would I need to build it out by parsing out hours, the remaining minutes, then remaining seconds and built a string with the results? Or is there a format mask?

Unfortunately, there is not such mask. You would need to build a formula that basically does what you described.

This old topic can help:

Convert number of seconds to actual time

We have a value in our database that is the number of seconds past midnight. Is there a way to convert this to hh:mm:ss in Business Objects (hh:mm would suffice) Many thanks in anticipation caz (BOB member since 2007-04-11)

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks Marek, I’ve done that. Is there a way that I can make this formula a generic function? ie format_time_as_interval(MyTimeAttribute) and it will return the string i want?

I dont relish copying and pasting the horrible formula i made all over the place.

---

twebber (BOB member since 2009-03-23)

[system]

Hi,

Sorry but it’s not possible to create functions in WebI reports.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks. I guess I could create columns for hours, minutes and seconds in a derived table then the formula would not be so long and complicated. But that adds lots of attributes to my uniiverse - about 30 new measures.

I see that there you can add functions to webi but you need to do this using c++ and the webi api.

---

twebber (BOB member since 2009-03-23)

[system]

updated formula with string padding.

=LeftPad(Trim(FormatNumber(Truncate(([T Talk]/[N Talk]) /3600;0);"##"))+"";2;“0”) + “:” + LeftPad(FormatNumber(Truncate(Mod(([T Talk]/[N Talk]) ;3600)/60;0);"##")+"";2;“0”) + “:” + LeftPad(FormatNumber(([T Talk]/[N Talk]) - (Truncate(([T Talk]/[N Talk] )/3600;0)*3600) - (Truncate(Mod(([T Talk]/[N Talk]) ;3600)/60;0)*60);"##")+"";2;“0”)

You would have to replace the [T Talk]/[N Talk] with whatever measure you have.

Output will be in format 00:00:00 for hours:minutes:seconds.

---

twebber (BOB member since 2009-03-23)