convert seconds to hours minutes seconds

building reports webintelligence
1

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)

2

Welcome to B:bob:B!

This can help:

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

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

3

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

BHeap (BOB member since 2009-05-29)

4

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 :india: (BOB member since 2007-10-24)

5

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?

6

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

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

7

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?

8
  • 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”)

2 Likes
9

That’s a great idea, thank you!