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

[Former Member]

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!!!

[amitrathi239 · Accepted solution]

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")

[Former Member · Accepted solution]

okay... will try - - so, tell me this - - am I to create two different variables; modify the one I have now to do the subtracting correctly... and then another one to convert the format to HH:MM?

[amitrathi239 · Accepted solution]

Yes i have used two variables.

one for subtracting and convert in seconds.

Second one is convert again seconds in HH.mm:ss

[Former Member · Accepted solution]

Thanks 🙂 I had figured out as much, after I left you the comment.

[former_member476872]

Hello Guys, I have something simple that works in some cases:

V_Status8610Minus1Hour

=RelativeDate([Status8610/Loc.Status Date/Time];-0.041666666)

Where (1/24 = 0.041666666) the negative reduce in 1 Hour:

Good luck

[Former Member]

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

An example:

What do you think about this?

[amitrathi239]

in this case you need to consider days between two dates also.otherwise your values are wrong.

It's right only when time difference is required where date values are same.

Added in below formula days difference.

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+ (DaysBetween([Date1];[Date2])*24)

[Former Member]

I got this to work.... by multiplying by the seconds (86400) where you have 24!!! Nice work again...lol!!!

[Former Member]

Okay... I don't know about that last comment I wrote now - - something funky is going on... it eliminated the negative numbers... but the timing is off... when I did the * 24, nothing happened... I still saw the negative numbers

[Former Member]

Okay.. I got it!!! Just need to adjust the number of seconds to only the hour (1440) not the day (86400)