BusinessObjects Board

Displaying elapsed time in decimal hours

building reports webintelligence
#1

Hi all,
Seeking assistance from the BOB gurus (as I’m clearly not) !

On report level in Webi XI version 3.1 (the only edit access available to me) I need a variable to calculate the DateDiff expressed in decimal hours.
On BOB I found 2 useful formulas that got me started in the right direction, but unfortunately, both provide end results that do not quite fit the requirement.

  1. Method by Dave Rathbun: https://bobj-board.org/t/21735
    does display the elapsed time, however appears to round it to the nearest hours.
    e.g. Time between 30/05/2011 20:30:11 and 30/05/2011 21:12:57 should be 0.70 hrs but it shows as 1.00 with me
=((( (ToNumber(SubStr(FormatDate(<DATE2> ,"HH:MM:SS") ,1 ,2))*60*60) + 
(ToNumber(SubStr(FormatDate(<DATE2> ,"HH:MM:SS"), 4, 2))* 60) + 
ToNumber(SubStr(FormatDate(<DATE2> ,"HH:MM:SS"), 7, 2))) - ( 
(ToNumber(SubStr(FormatDate(<DATE1> ,"HH:MM:SS") ,1 ,2))*60*60) + 
(ToNumber(SubStr(FormatDate(<DATE1> ,"HH:MM:SS"), 4, 2))* 60) + 
ToNumber(SubStr(FormatDate(<DATE1> ,"HH:MM:SS"), 7, 2)))) / 3600) + 
(DaysBetween(<DATE1> ,<DATE2>)*24)

2.Another useful set of formulas from Marek Chladny:https://bobj-board.org/t/117814
do show the actual hours, minutes and seconds elapsed, but here the problem is that the field in BO is text.
It seems that I can’t format it any different to get to the decimal value.
e.g. Time between 30/05/2011 20:30:11 and 30/05/2011 21:12:57 is 0.70 hrs but it shows as 00:42:46 (as a text cell) with me.

Code:
=DaysBetween(<ADMIT_DATE> ,<ORDER_DATE>) * 86400 
+ 
( 
   ToNumber(FormatDate(<ORDER_DATE> ,"HH")) * 3600 + 
   ToNumber(Left(FormatDate(<ORDER_DATE> ,"mm:ss") ,2)) * 60 + 
   ToNumber(FormatDate(<ORDER_DATE> ,"ss")) 
) 
- 
( 
   ToNumber(FormatDate(<ADMIT_DATE> ,"HH")) * 3600 + 
   ToNumber(Left(FormatDate(<ADMIT_DATE> ,"mm:ss") ,2)) * 60 + 
   ToNumber(FormatDate(<ADMIT_DATE> ,"ss"))

Followed by a second variable:

=FormatNumber(Floor(<number of seconds>/86400) ,"0") &amp; " day(s) " &amp; 
FormatNumber(Floor(Mod(<number of seconds> ,86400)/3600) ,"00") &amp; ":" &amp; 
FormatNumber(Floor(Mod(Mod(<number of seconds> ,86400) ,3600)/60) ,"00") &amp; ":" &amp; 
FormatNumber(Mod(Mod(Mod(<number of seconds> ,86400) ,3600) ,60) ,"00")

But as I said the output is text and not in decimal hours.

Hope anyone can offer help with this.

Many thanks.

//MG

MGinUK :netherlands: (BOB member since 2011-06-09)

#2

Hi,

Take my first formula. It will return the number of seconds between 2 dates.

Then divide the result by 3600 (the number of seconds in 1 hour) and you will get the difference between the 2 dates in hours. Use rounding if you want only certain number of decimal places in the result.

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

#3

Wow - you :mrgreen: guys are good ! - it works. Many thanks.
//MG

MGinUK :netherlands: (BOB member since 2011-06-09)