Format number as HH:mm:ss

building reports desktopintelligence
1

I have a column in a report which is a number of seconds containing values like 24, 122, -45, etc
Is it possible to display this column in the format HH:mm:ss, like this : 24 00:00:24
122 00:02:02
-45 -00:00:45

Thanks in advance for any help/info/tip

Pierrot

__________________________________________________________ Pierrot HERITIER
Swiss Federal Railways, Mittelstrasse 43, CH-3030 Bern (Switzerland) Tel (++41) (512) 20 4484
Fax (++41) (512) 20 4485
e-mail : pher@royal.net

Listserv Archives (BOB member since 2002-06-25)

2

Pierrot Heritier wrote:

I have a column in a report which is a number of seconds containing values like 24, 122, -45, etc
Is it possible to display this column in the format HH:mm:ss, like this : 24 00:00:24
122 00:02:02
-45 -00:00:45

Try the following formula:

=FormatNumber(Mod((-Mod( ,1440))/1440,1440),“00”) & “:” & right(FormatNumber(Mod((-Mod( ,60))/60,24),“00”),2) & “:” & right(FormatNumber(Mod( ,60),“00”),2)

Note I initially tried using absolute values to get rid of the negatives for hours and minutes, but frequently encountered an absolute value of -0. I’m assuming this is a bug (v4.1.2) related to the data precision being used in Busines Object’s internal calculations.

FYI,

Bob Molby
GE Lighting

Listserv Archives (BOB member since 2002-06-25)

3

I have a column in a report which is a number of seconds containing values like 24, 122, -45, etc
Is it possible to display this column in the format HH:mm:ss, like this : 24 00:00:24
122 00:02:02
-45 -00:00:45

Try the following formula:

=FormatNumber(Mod((-Mod( ,1440))/1440,1440),“00”) & “:” & right(FormatNumber(Mod((-Mod( ,60))/60,24),“00”),2) & “:” & right(FormatNumber(Mod( ,60),“00”),2)

Note I initially tried using absolute values to get rid of the negatives for hours and minutes, but frequently encountered an absolute value of -0. I’m assuming this is a bug (v4.1.2) related to the data precision being used in Busines Object’s internal calculations.

Bob Molby
GE Lighting

Many thanks. It works fine for 24 and 122, but the “-” sign is not displayed for -45 seconds : I get “00:00:45” when I should get “-00:00:45”. What’s the trick to do this ?

Pierrot

Listserv Archives (BOB member since 2002-06-25)

4

Many thanks. It works fine for 24 and 122, but the “-” sign is not displayed for -45 seconds : I get “00:00:45” when I should get
“-00:00:45”.
What’s the trick to do this ?

Pierrot

You are right. I had only used this on larger numbers. I quickly altered the formula to multiply the sign of the number * the absolute value of the first component and it seems to work. Here is the revised formula:

=FormatNumber(sign()*abs(Mod((-Mod( ,1440))/1440,1440)),“00”) & “:” & Right(FormatNumber(Mod((-Mod( ,60))/60,24),“00”),2) & “:” & Right(FormatNumber(Mod( ,60),“00”),2)

FYI,

Bob Molby
GE Lighting

Listserv Archives (BOB member since 2002-06-25)

5

Try adding this between the “=” and the first “FormatNumber” in the formula:

SubStr( “-”, 1, -1*Sign() ) &

Erich Hurst
Compaq Computer Corporation
(281) 514-9445
Erich.Hurst@Compaq.com

Try the following formula:

=FormatNumber(Mod((-Mod( ,1440))/1440,1440),“00”) & “:” & right(FormatNumber(Mod((-Mod( ,60))/60,24),“00”),2) & “:” & right(FormatNumber(Mod( ,60),“00”),2)

Many thanks. It works fine for 24 and 122, but the “-” sign is not displayed for -45 seconds : I get “00:00:45” when I should get “-00:00:45”.
What’s the trick to do this ?

Listserv Archives (BOB member since 2002-06-25)