convert seconds to hours minutes seconds

[system]

Hi

I have a field which contains seconds as an integer. Is there a way I can display this as hh:mm:ss in web intelligence? eg 60 --> 00:01:00

I can do this in crystal and excel but can’t find anything in webi.

Any help gratefully received.

---

BHeap (BOB member since 2009-05-29)

[system]

Welcome to B:bob:B!

This 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)

The formula in the linked post is for DeskI but it should not be difficult to rewrite it for WebI.

---

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

[system]

Thank you very much! That was just what I was looking for.

---

BHeap (BOB member since 2009-05-29)

[system]

its…

=FormatNumber(Floor(LastExecutionDuration([Query 1])/3600);"00") + ":"+FormatNumber(Floor(Mod(LastExecutionDuration([Query 1]);3600)/60);"00") + ":"+ 
FormatNumber(Mod(Mod(LastExecutionDuration([Query 1]);3600); 60);"00")

---

Vills (BOB member since 2007-10-24)

[Foster]

Hi,

Not sure if this thread is too old or if I should start another.

I am trying to convert seconds to hh:mm:ss and have the following formula.

=FormatNumber(Floor(Mod([Time Diff] ;86400/3600);“00”) + “:” +

FormatNumber(Floor(Mod(Mod(Abs([Time Diff]) ;86400);3600)/60);“00”) + “:” +

FormatNumber(Mod(Mod(Mod(Abs([Time Diff]) ;86400) ;3600) ;60);“00”)

However, there are several instances where my [Time Diff] is negative. It appears to be adding an additional -1 hour.

Example: -4568 seconds gets me -2:16:08

What am I missing?

[N8AKTIV]

… maybe some unexpected results with the floor()-function if value is negative ?!

pay attention to:
floor(10.5) --> 10
floor(-10.5) --> -11

[Foster]

That’s a good point.

Changing it the formula below does make it better but there are still a few unexpected results.

Example:
-12,808 seconds gets -04:33:28
-2,204 seconds gets -01:37:44

=FormatNumber(Mod([Time Diff] ;86400)/3600;“00”) + “:” +

FormatNumber(Mod(Mod(Abs([Time Diff]) ;86400);3600)/60;“00”) + “:” +

FormatNumber(Mod(Mod(Mod(Abs([Time Diff]) ;86400) ;3600) ;60);“00”)

Any ideas?

[N8AKTIV]

• do not delete the floor() function!
• add abs() to the first part to avoid the wrong hour-floor-calculation if negative value
• finally add the prefix “-” if the number is negative

=If([Time Diff] < 0;"-";"")+
FormatNumber(Floor(Mod(Abs([Time Diff]) ;86400)/3600);“00”) + “:” +
FormatNumber(Floor(Mod(Mod(Abs([Time Diff]) ;86400);3600)/60);“00”) + “:” +
FormatNumber(Mod(Mod(Mod(Abs([Time Diff]) ;86400) ;3600) ;60);“00”)

[Foster]

That’s a great idea, thank you!