subtracting two dates and times

[#1]

l would like to subtract 2 dates and times. What formula do l use in qlikview? Last drop departure date minus Last drop arrival date

Last Drop Arrival Date	Last Drop Departure Date
28/10/2015 06:15	28/10/2015 7:45
28/10/2015 07:30	28/10/2015 09:30
29/10/2015 09:50	29/10/2015 11:30
28/10/2015 21:00	29/10/2015 08:00

[oknotsen]

Assuming those fields are not text fields, you can just do one minus the other:

[Last Drop Arrival Date] - [Last Drop Departure Date]

The end result will be the number of days it took between those dates.

Since you result seems to be mainly a difference in hours, you could multiply that result by 24 to get the number of hours:

([Last Drop Arrival Date] - [Last Drop Departure Date]) * 24

... and round that to the number of decimals you like...:

round(([Last Drop Arrival Date] - [Last Drop Departure Date]) * 24, 0.01)

[#3]

Hi;

Thanks

My format is a general format. What formula do l use to incorporate date format?

[Anonymous]

Also the Interval() function could be useful to you.  Below pasted from the QV Desktop Help :

Interval

interval(expression [ , format-code ])

The interval function formats the expression as a time interval according to the string given as a format-code. If the format code is omitted, the time format set in the operating system is used. Intervals may be formatted as a time, as days or as a combination of days, hours, minutes, seconds and fractions of seconds.

Examples:

The examples below assume the following operating system settings:

Short date format:	YY-MM-DD
Time format:	hh:mm:ss
Number decimal separator:	.

interval( A ) where A=0.375 returns:

String	09:00:00
Number	0.375

interval( A ) where A=1.375 returns:

String	33:00:00
Number	1.375

interval( A, 'D hh:mm' ) where A=1.375 returns:

String	1 09:00
Number	1.375

interval( A-B, 'D hh:mm' ) where A=97-08-06 09:00:00 and B=96-08-06 00:00:00 returns:

String	365 09:00
Number	365.375

QlikView 11.20 SR11

[Anonymous]

Format it with the Interval() function.

[oknotsen]

That "general format" sounds like something from Excel. If you convert it to a number in Excel, will it show a number or still text?

In Qlik data is (to keep it easy) a number of text. If it is a number you can do calculations with it. A date secretly is a number, just formatted as a date. You can format every number as a date by just putting the date() function around it in your load script.

[Kushal_Chawda]

try

interval( [Last Drop Departure Date]-[Last Drop Arrival Date],'hh:mm') as Interval

[Anonymous]

Hi Moses,

Try this:

num#(Interval(timestamp#(timestamp(Last Drop Departure Date,'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm:ss')

              -timestamp#(timestamp(Last Drop Arrival Date,'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm:ss')

             ,'m')) as Interval

[swuehl]

Please have a look at

Why don’t my dates work?‌

Get the Dates Right

[#10]

What is the end result you want to see?

Is it the difference in time? number of days and hours?

or something else?