calculate difference between two times in webi

system · December 14, 2010, 2:36am

Hi All

I want to calculate the difference between two times in report level.

For example:4:00 to 4:20
I need to show the difference between the two times is 20 minutes in the report.How can i achieve the same?

I tried to find in BOB forums but they used dates and calculated the difference of time in Deski.

Please help to create a variable at report level in Webi.

Thanks in advance.I appreciate your work.

passport (BOB member since 2010-05-07)

system · December 14, 2010, 6:51am

= 
(
 ( 
 ( 
 ToNumber( Substr( FormatDate( [Most recent timestamp] ;"HH:mm:ss" ) ; 1 ; 2 ) ) * 60 * 60 +
 ToNumber( Substr( FormatDate( [Most recent timestamp] ;"HH:mm:ss" ) ; 4 ; 2 ) ) * 60 + 
 ToNumber( Substr( FormatDate( [Most recent timestamp] ;"HH:mm:ss" ) ; 7 ; 2 ) )
 ) 
 - 
 (
 ToNumber( Substr( FormatDate( [Oldest timestamp] ; "HH:mm:ss" ) ; 1 ;2 ) ) * 60 * 60 +
 ToNumber( Substr( FormatDate( [Oldest timestamp] ; "HH:mm:ss" ) ; 4 ; 2) ) * 60 +
 ToNumber( Substr( FormatDate( [Oldest timestamp] ; "HH:mm:ss" ) ; 7 ; 2) )
 )
 )
) / 60 +
( DaysBetween( [Oldest timestamp] ; [Most recent timestamp] ) * 24 * 60 )

mikca (BOB member since 2005-12-16)

system · December 14, 2010, 4:16pm

Thanks a Ton…That worked…You are awesome…!!!

passport (BOB member since 2010-05-07)

system · December 14, 2010, 9:39pm

Thanks so very much!
I had the same kinda issue,your formula worked great!

heyyin (BOB member since 2010-12-14)

system · December 14, 2010, 10:54pm

It is a formula that I found on BOB so gratitude to original author whose name I lost

I find it strange that BO does not have a built in TimeStamp_Diff routine

Mick

mikca (BOB member since 2005-12-16)

system · December 15, 2010, 8:25pm

Yeah Man, i am new to WEBI and i wasted some time writing something to it…
hope the next realease has it.

heyyin (BOB member since 2010-12-14)

system · February 7, 2011, 10:25pm

Desperately needed this and very glad I came across this post. Thanks!

steph m (BOB member since 2008-04-11)

system · June 23, 2012, 4:59am

THIS May be the answer I have been looking for…or at least on the right track.

I changed the names of the fields to match my fields. [Start Time] would replace [oldest time stamp] and [End Time] would replace [Newest Time Stamp]? Right?

And if the time is labeled as a text column, how can I change it? It will not let me select any number options in the format cell menu.

I have spent hours to get a duration, and finally found this forum, so I am really hoping you can help!

THANK YOU!!!

bmorehappy (BOB member since 2012-06-23)

system · June 23, 2012, 10:52am

If your timestamp is already a text field then you can remove all of the FormatDate routines (which only exist to turn timestamps to strings) and use appropriate ToNumber( Substr( [Your datetime field value]; n; m) ) routines to extract the values you want.

Mick

mikca (BOB member since 2005-12-16)

system · January 30, 2018, 3:14pm

Thanks

cheedalla (BOB member since 2004-04-13)

system · February 7, 2018, 11:19am

4.2 SP5 has new inbuilt functions for calculating time differences

jemstar (BOB member since 2006-03-30)