# convert seconds to hours minutes seconds

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.

BHeap (BOB member since 2009-05-29)

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 (BOB member since 2003-11-27)

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

BHeap (BOB member since 2009-05-29)

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)

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?

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

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

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?

• 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

Thatβs a great idea, thank you!