Agreed.
If you need to calculate a TimeBetween() result in BusinessObjects, the generally accepted way is to convert the entire date/time value into one unit (hours or seconds), do the math, and then conver the result back into the desired unit. Using FormatDate() you can convert the date value into a string. Using Substr() you can extract portions of the string. Using ToNumber() you can convert those string elements into numbers, and then convert the numbers into hours (minutes, seconds, whatever) to do the subtraction.
Convert the final result back into the desired time unit and you’re done. 8)
Here’s a sample:
=((( (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)
This converts two dates to their components, converts everything to seconds, subtracts, then converts back to hours for the final output. This is from an older “Variables” presentation that I did at a user conference… the entire presentation is online for review if you like.
Dave
Dave Rathbun (BOB member since 2002-06-06)