Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Subtract two timestamps to output difference in minutes

Can anyone assist in subtracting two date/time fields to output difference in minutes.

DD/MM/YYYY hh:mm:ss is my format, but I want it to output just the minutes not the seconds and if it goes into an hour instead of showing 01:10 display 70

1 Solution

Accepted Solutions
Gysbert_Wassenaar

interval(date1-date2,'mm')

Make sure date1 and date2 are really datetimes, use the timestamp# and/or timestamp functions as necessary.


talk is cheap, supply exceeds demand

View solution in original post

18 Replies
Gysbert_Wassenaar

interval(date1-date2,'mm')

Make sure date1 and date2 are really datetimes, use the timestamp# and/or timestamp functions as necessary.


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks for the response again Gysbert, they are dates and times picture 1.png

Not applicable
Author

=Minute(Frac(Timestamp1)-Frac(Timestamp2))

i think it ll help u

Not applicable
Author

Hi thanks for the response I tried implementing your suggestion to no avail screenshot below.

pincture 2.png

Not applicable
Author

Is there any other configuration I need to do first. the field SCH and CHKS are defined within my excel data set as dd/mm/yyyy hh:mm:ss

so far nothing I have tried has rendered a sufficient output

hic
Former Employee
Former Employee

First, check that QlikView has interpreted these dates correctly by formatting them as numbers (ListBox properties -> Numbers). If they are displayed as numbers with values around 40000, then they are correctly interpreted. These numbers are number of days since Dec 30, 1899.

Then you should be able to subract one date from another: Date1 - Date2. Let's assume that the result is 0.1, i.e. 10% of a day, i.e. 2 hours 24 minutes.

Then you can choose to format this number in minutes: Minute(Date1-Date2), which will keep the value 0.1 but display 144, Or, you can convert it to numerical minutes: (Date1-Date2)*24*60

HIC

Not applicable
Author

Hi thanks fior the response,

My fields are formatted dd/mm/yyyy hh:mm:ss so contains both date and time, however only the hh:mm of the time is displayed in the field itself.

I have gone through the formatting and at present it is formatted to fixed to - 2 Decimals.

swuehl
MVP
MVP

Then you can choose to format this number in minutes: Minute(Date1-Date2), which will keep the value 0.1 but display 144, Or, you can convert it to numerical minutes: (Date1-Date2)*24*60

Hi Henric,

I believe minute() function will always return the minutes part of the interpreted time, so given that 0.1 is interpreted as time 02:24:00 (represented in my standard format),

=minute(0.1)

will return 24, not 144, right? I believe you need to use interval() function for this kind of representation (as Gybert already suggested above):

=interval(0.1,'mm')

sandston, have you checked that your timestamp values are correctly interpreted when reading in from your source (i.e. as formatting them as numbers, like Henric suggested)?

If you don't get numeric values displayed, please check the timestamp interpretation function timestamp#() in the HELP.

As I am writing this, Now() returns 41232.498368056, so f.e.

=interval(41232.498368056 - 41232.448368056,'mm')

should return 72 (which is that two values time difference in minutes, in the format you requested).

Regards,

Stefan

hic
Former Employee
Former Employee

You're right Stefan. Well spotted.

HIC