Former Member

# subtract time in Webi (where I am able to display both Hours and minutes)

I would like to be able to subtract two timestamps in my Webi report, but I want to be able to display the final result in HH:MM format

Currently, I am using the following:

= ( ( (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 )))) - ((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))) ) ) / 60 / 60* -(1)

Originally, I divided by 60 (and this converted the final equation in minutes); then I was asked to show the hours (so I divided again by 60)... but I'm getting only the hours now... when I went to change my Numbering format, I was only able to convert to a decimal... which is also what I'm not trying to do... is what I am trying to accomplish possible? I am not sure... oh, the reverse sign thing is because I'm getting some negative number which shouldn't be the case, so I'm also trying to figure that out.

Thanks!!!

10|10000 characters needed characters exceeded

Apr 05, 2017 at 08:46 AM

you are getting negative result because you are subtracting from Date 1.you need to subtract from Date2.

number of seconds= ( ( (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))) ) ) / 60 / 60

After that use this to convert seconds in HH:mm:ss

=FormatNumber(Floor([number of seconds]/3600) ;"0") + ":" + FormatNumber(Floor(Mod([number of seconds];3600)/60);"00") + ":" + FormatNumber(Mod(Mod([number of seconds] ;3600) ;60) ;"00")

10|10000 characters needed characters exceeded
• Former Member

Thanks :-) I had figured out as much, after I left you the comment.

• Former Member
Apr 05, 2017 at 01:51 PM

Thank you Kumar!!! That works - - I am still getting some negative numbers, when the dates cross over days if that makes since...

An example: