/scripts/ahub.form.attachments.js
0

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

Apr 04, 2017 at 09:00 PM

143

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

AMIT KUMAR
Apr 05, 2017 at 08:46 AM
0

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

Show 3 Share
Former Member

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?

Former Member

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

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

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

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

An example:

snippet-time.png (2.1 kB)
Show 4 Share

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)