calculating difference in DateTimes from two different sources

Margaret — Fri, 29 Sep 2017 15:45:46 GMT

I need to calculate the number of minutes between two times that may be on different days. So if one is 23:30 last night, and the other is 1:30 this morning, the calculation's result needs to be: 120 minutes.

I am getting one DateTime value from SQL Server (YYYY/MM/DD hh:ss) and one from Excel.

I need a way to do one of the following:

Tell Excel to format the DateTime as YYYY/MM/DD hh:ss (I recognize this is beyond the scope of this forum).
Tell QlikView to convert the DateTime in Excel (MM/DD/YYYY hh:ss) to YYYY/MM/DD hh:ss, so that it matches all the other date times in the .qvw.
Set Analysis that calculates the difference between one date and time and another date and time (so if one is 23:30 last night, and the other is 1:30 this morning, the calculation's result needs to be: 120 minutes).

Thank you!!

Re: calculating difference in DateTimes from two different sources

rubenmarin — Fri, 29 Sep 2017 16:08:09 GMT

Hi Margaret, Timestamp# can be used to convert date formats,

Timestamp#(FieldInOtherFormat, 'MM/DD/YYYY hh:mm') // returns the timestamp in the document format

To get the difference in minutes you can substrat one date to another and multiply the result by 1440 (minutes in a day):

(EndDateTime-StartDateTime) * 1440 as MinutesDifference

topic calculating difference in DateTimes from two different sources in QlikView

calculating difference in DateTimes from two different sources

Re: calculating difference in DateTimes from two different sources