BusinessObjects Board

Converting a Number to HH: MM Format

building reports webintelligence
#1

Hi,

I have a situation where in a numeric column needs to be represented in HH:MM format. The value in the numeric column comes out as minutes

Example : If the value of the column is 300. We need to treat 300 as minutes .

300 needs to be represented as 05 : 00 format. Similarly
330 needs to be represented as 330 /60 = 5.5 which will be 05 : 30
380 needs to be represented as 380 /60 = 6.33 which should be 06 :20

I cannot convert the numeric field to datetime …but need to get the solution based on this information.

I have triied implementing the if logic in the webi

=If( Right([column];2)< 60 ; [column]/60 + " : " + Right([column]; 2) ; [column]/60 + " : " + Right([column]; 2)-60 )

This throws me invalid data type error. I figured this out …as I am implemeting srting operation on a numeric data type…
I would request if I can be guided about any alternate method to achieve this.

Thank You,
dpad

dpad :us: (BOB member since 2010-05-10)

#2

have a look here

http://www.bobti.com/en/ref_businessobjects.htm

bernard timbal :fr: (BOB member since 2003-05-26)

#3

Hi Dpad,

This is your code

Rajubollas :india: (BOB member since 2009-09-04)

#4

Thank You Raju for your prompt response.

I tried implementing it with using the said functions and format …but it throws me Invalid Input error just before the pound sign “#####”

Below is the code that I am using :

=If(Right(FormatNumber([Difference From Actual In And Login Minutes];"###") ;2)< 60;(Floor(ToNumber(Right(FormatNumber([Difference From Actual In And Login Minutes];"###");2))/60))

  • “:” + ToNumber(Right(FormatNumber([Difference From Actual In And Login Minutes];"###");2));(Floor(ToNumber(Right(FormatNumber([Difference From Actual In And Login Minutes];"###");2))/60))
  • " : " + (ToNumber(Right(FormatNumber([Difference From Actual In And Login Minutes];"###");2))-60))

Is it because of the version of the webi that I am using…though does not seems so…I am on BOE XI release 2. Please let me know.

Thank You,
dpad

dpad :us: (BOB member since 2010-05-10)

#5

Hi Dpad,

[quote]=If(Right(FormatNumber([Difference From Actual In And Login Minutes];"###") ;2)< “60”;(Floor(ToNumber(Right(FormatNumber([Difference From Actual In And Login Minutes];"###");2))/60))

  • “:” + ToNumber(Right(FormatNumber([Difference From Actual In And Login Minutes];"###");2));(Floor(ToNumber(Right(FormatNumber([Difference From Actual In And Login Minutes];"###");2))/60))
  • " : " + (ToNumber(Right(FormatNumber([Difference From Actual In And Login Minutes];"###");2))-60))[quote]

Implement very carefully then it will work fine.

This exactly Yours

Rajubollas :india: (BOB member since 2009-09-04)

#6

Hi,

Here is a formula for DeskI:

Just convert it for WebI :slight_smile:

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

#7

Thanks Marek a ton…it worked for me in webi… 8)

dpad :us: (BOB member since 2010-05-10)