Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
cosmicyes
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
marcus_sommer

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
Anil_Babu_Samineni

May be add minutes as format

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
cosmicyes
Contributor III
Contributor III
Author

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

marcus_sommer

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

tresesco
MVP
MVP

One common mistake:

MM - month

mm - minute

So, use 'mm' and 'MM' properly

cosmicyes
Contributor III
Contributor III
Author

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

Perfect - thank you so much!