Difference in Time only (No Timestamp)

[#1]

Need to calculate the time difference between two time considering the AM and PM factor.

My time are in this format

Start = 11:55:37 PM

End  = 1:10:33 AM

I know I have to use Interval() but I am getting only negative values as the AM PM is not being considered. I also need to display the total difference in minutes.

[Anonymous]

= interval ( time# ( '11:55:37 PM' , 'h:mm:ss tt' ) - time# ( '1:10:33 AM'  , 'h:mm:ss tt' ) , 'hh:mm:ss' )

[maxgro]

in minutes

(time#('11:55:37 PM', 'hh:mm:ss tt') - time#('1:10:33 AM', 'hh:mm:ss tt')) * 24*60

or

interval(time#('11:55:37 PM', 'hh:mm:ss tt') - time#('1:10:33 AM', 'hh:mm:ss tt'), 'mm')

[#4]

Thank you for the reply Bill but i'm still not getting the right results. Hope the below snap helps clarify my predicament:

On the first one the answer difference should have been 00:14:04 which is not the case

[Anonymous]

Try subtracting the Start time from the End time.  Looks like you maybe doing it the other way round.

[Peter_Cammaert]

Add a date part to the underlying time value and your problem will be solved.

QlikView will consider 12:10:33 AM to be the morning of the same day, because the date value = 0. In your case, you would like to have this date part to be at least = 1...

Peter

[MarcoWedel · Accepted solution]

Hi,

the best way would be to subtract timestamps instead of time values (including the date part as already suggested).

If this information is not available, then you can imply that the difference has to be positive and less than 1 (day).

So one possible solution could be:

=Interval(fmod(End-Start+1,1))

given your time values are already loaded as dual values (i.e. numerical formatted as time) like your screenshot suggests.

If your time fields are simply text, then you have to convert them (again as already suggested) like:

=Interval(fmod(Time#(End,'hh:mm:ss TT')-Time#(Start,'hh:mm:ss TT')+1,1))

hope this helps

regards

Marco

[#8]

Marco that is exactly what I needed. Thank you so much.

[MarcoWedel]

you're welcome

regards

Marco