Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Calculate duration in minutes

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

 

Labels (1)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

6 Replies
Highlighted

May be add minutes as format

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

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....

Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

Highlighted
MVP
MVP

One common mistake:

MM - month

mm - minute

So, use 'mm' and 'MM' properly

Highlighted
Contributor III
Contributor III

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

Perfect - thank you so much!