topic Re: Calculate duration in minutes in QlikView

Calculate duration in minutes

cosmicyes — Sat, 16 Nov 2024 20:12:06 GMT

Hello friends,

I need to calculate the duration between 2 timestamps in minutes (even if it lasts for days).

In my Excel I have:

startdate (dd.mm.yyyy)
starttime (hh:mm)
enddate (dd.mm.yyyy)
endtime (hh:mm)

What I have tried so far:

Interval((date(enddate,'YYYYMMDD') & time(endtime,'HHMM'))-(date(startdate,'YYYYMMDD') & time(starttime,'HHMM'))) as Duration,

Example:
Startdate: 01.08.2018
Starttime: 00:05
Enddate: 01.08.2018
Enddate: 06:05

My solution leads to 600.
The right solution would be 360.
But as 360 minutes are 6 hours I feel that I am not far from the right solution...

Any help?

Thank you in advance

Re: Calculate duration in minutes

Anil_Babu_Samineni — Mon, 26 Aug 2019 12:33:41 GMT

May be add minutes as format

Interval((date(enddate,'YYYYMMDD') & time(endtime,'HHMM'))-(date(startdate,'YYYYMMDD') & time(starttime,'HHMM')), 'MM') as Duration,

Re: Calculate duration in minutes

cosmicyes — Mon, 26 Aug 2019 12:39:39 GMT

Thank you - but no, same result.

I did another try:

date((date(enddate,'YYYYMMDD') & time(endtime, 'hhmm'))-(date(startdate,'YYYYMMDD') & time(starttime, 'hhmm')))/100*60 as Duration

In my first example this leads to 360 --> cool.
But:
Another example
startdate 01.08.2018
starttime 00:35
enddate 01.08.2018
endtime 01:24

my second try leads to 53 but 49 would be right. Mmmm....

Re: Calculate duration in minutes

marcus_sommer — Mon, 26 Aug 2019 12:44:46 GMT

If your fields are real dates and times there is no need to format them - if not you need to convert them with date#() and time#(). To merge them they are added like date + time because & is for a string-concatenation (even if it's regocnized in some cases as a number). Further you need to apply the right formatting which are set in lower cases for times. In short try:

Interval((enddate + endtime)-(startdate + starttime),'mm')

- Marcus

Re: Calculate duration in minutes

tresB — Mon, 26 Aug 2019 12:45:53 GMT

One common mistake:

MM - month

mm - minute

So, use 'mm' and 'MM' properly

Re: Calculate duration in minutes

cosmicyes — Mon, 26 Aug 2019 13:05:50 GMT

Thank you. I did not know this.
Or I knew and forgot 😉

Re: Calculate duration in minutes

cosmicyes — Mon, 26 Aug 2019 13:06:27 GMT

Perfect - thank you so much!