calculating difference in DateTimes from two different sources

[Margaret]

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:

1. Tell Excel to format the DateTime as YYYY/MM/DD hh:ss (I recognize this is beyond the scope of this forum).
2. 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.
3. 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!!

[rubenmarin1]

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