topic Minutes between 2 times in QlikView

Minutes between 2 times

johnan — Tue, 16 Jun 2009 16:45:14 GMT

Hi All!

I have 2 time colums and i want to calculate the minutes between this times:

08:00 between 08:45 should give me - 45 minutes
07:00 between 06:50 should give me + 10 minutes

Any way to do this?

Minutes between 2 times

Tue, 16 Jun 2009 17:57:54 GMT

Hi,

Use this expression to get a result in seconds:

round((TimeStamp1-TimeStamp2)*86400)

or to get a result in mins:

round((TimeStamp1-TimeStamp2)*1440)

Cheers

Karol

Minutes between 2 times

johnan — Tue, 16 Jun 2009 18:39:37 GMT

Hmm, it doesent work.

The first column is a timeformat from Excel and called Levtime
Exampel:
Levtime 10:00

And the other isa timestamp but i have format this in the script time(Status) as [Statustime]

Statustime = 10:01:00
And the result of:
round((Levtime-Statustime)*1440) gives me: -57 571 201

Minutes between 2 times

Tue, 16 Jun 2009 20:09:14 GMT

Here is an example that works.

Philippe

Minutes between 2 times

johnan — Tue, 16 Jun 2009 20:32:44 GMT

Okay, it's seems that my formating is wrong. But i works now.

How to format in HH?

Minutes between 2 times

Tue, 16 Jun 2009 20:44:11 GMT

Replace 1440 by 24

Philippe

Minutes between 2 times

johnan — Wed, 17 Jun 2009 14:38:03 GMT

How to calculate 13:00 + 60 minutes = 14:00?

Minutes between 2 times

Wed, 17 Jun 2009 15:34:54 GMT

Hi,

it dependens on the underlying datatypes....

put this expression in the textbox and check how it works:

=time((num(time('13:00'))+1/24)) //to calculate 13:00 +60mins

cheers

Karol

Minutes between 2 times

johnan — Wed, 17 Jun 2009 15:58:13 GMT

Hi, hmm this wont work

13:00 is a Datetime datatype and 60 is number. I want QV to calculate the above to 14:00 (13:00 + 60 minutes)

Minutes between 2 times

Wed, 17 Jun 2009 16:13:19 GMT

So what number is under 13:00 datetime value? Can you do num(YOUR_DATETIME_FIELD) and paste the value.

Cheers

Karol

Minutes between 2 times

johnan — Wed, 17 Jun 2009 16:46:16 GMT

Hi,

11:22 is

0,47361111111111 in numbers

Minutes between 2 times

Wed, 17 Jun 2009 17:11:07 GMT

ok. so this number represent the following date and time:

30/12/1899 11:22:00

which can be easily veryfied:

=date(0.47361111111111,'DD/MM/YYYY hh:mm:ss[.fff]')

If you want to add 60 minutes just use this expression:

=time(num(YOUR_DATETIME_FIELD)+1/24)

Becasue all dates in qv have numeric representation you can use this expression:

=time(YOUR_DATETIME_FIELD+1/24)

or simply:

=YOUR_DATETIME_FIELD+1/24 and then apply a proper format using date or time function.

The above expressions work fine. If you still have a problem submit the example of your Qv applicaiton.

Cheers

Karol

Minutes between 2 times

Tue, 17 Apr 2012 15:37:12 GMT

Do you have some practical example how to do it?

//JJ