/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

119

avatar image

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 left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
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
10 |10000 characters needed characters left characters exceeded

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?

1

Yes i have used two variables.

one for subtracting and convert in seconds.

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

0

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

0
Shelafia Ingram 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:

What do you think about this?


snippet-time.png (2.1 kB)
Show 4 Share
10 |10000 characters needed characters left characters exceeded

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)

1

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

1

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

0

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

1
Skip to Content