# Displaying elapsed time in decimal hours

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:
+
(
ToNumber(FormatDate(<ORDER_DATE> ,"HH")) * 3600 +
ToNumber(Left(FormatDate(<ORDER_DATE> ,"mm:ss") ,2)) * 60 +
ToNumber(FormatDate(<ORDER_DATE> ,"ss"))
)
-
(
ToNumber(Left(FormatDate(<ADMIT_DATE> ,"mm:ss") ,2)) * 60 +

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 (BOB member since 2011-06-09)

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

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

MGinUK (BOB member since 2011-06-09)