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!!!
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")
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?
Yes i have used two variables.
one for subtracting and convert in seconds.
Second one is convert again seconds in HH.mm:ss
Thanks :-) I had figured out as much, after I left you the comment.
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?
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)
I got this to work.... by multiplying by the seconds (86400) where you have 24!!! Nice work again...lol!!!
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
Okay.. I got it!!! Just need to adjust the number of seconds to only the hour (1440) not the day (86400)