Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
carlcimino
Creator II
Creator II

Duration Between Two Dates/Times

Hello, I am trying to calculate the time (in minutes) between a flight departure and a flight arrival.  I know I have to merge the dates and time fields.  However I'm unsure how to convert the time field to an actual time because of the A or P appended onto the end to designate AM or PM.   Any help would be appreciated.

Here is some sample data:

FlightDateTimeDuration:
load * inline [
FLIGHT DEP DATE,FLIGHT DEP TIME,FLIGHT ARRIVAL DATE,FLIGHT ARRIVAL TIME
05/24/2021,0100P,05/24/2021,0235P
04/22/2020,0105P,04/22/2020,0420P
05/26/2021,0115P,05/26/2021,0325P
06/09/2021,0115P,06/09/2021,0232P
03/20/2020,0120P,03/20/2020,0255P
10/03/2021,0130P,10/03/2021,0324P
05/20/2021,0137P,05/20/2021,0350P
06/17/2021,0157P,06/17/2021,0305P
05/19/2021,0200P,05/19/2021,0340P
05/28/2021,0200P,05/28/2021,0325P
06/02/2021,0200P,06/02/2021,0305P
05/27/2021,0213P,05/27/2021,0355P
06/11/2021,0213P,06/11/2021,0329P
05/28/2021,0220P,05/28/2021,0430P
05/26/2021,0230P,05/26/2021,0405P
05/19/2021,0233P,05/19/2021,0438P
05/28/2021,0235P,05/28/2021,0435P
05/27/2021,0240P,05/27/2021,0725P
06/14/2021,0243P,06/14/2021,0552P
06/05/2021,0245P,06/05/2021,0431P
05/26/2021,0249P,05/26/2021,0535P
06/18/2021,0255P,06/18/2021,0439P
05/24/2021,0300P,05/24/2021,0445P
]
;

1 Solution

Accepted Solutions
carlcimino
Creator II
Creator II
Author

Solved it.  Hope this helps!  A little bit of additional date/time formatting required based on the source system data fields.  The stumbling block was the format on the time fields to take the A or P into consideration --> time(Time#([TFATME],'hhmmt'),'hh:mm tt')

, interval(Timestamp(Date(Date#(TFADTE, 'YYYYMMDD'), 'MM/DD/YYYY') + time(Time#([TFATME],'hhmmt'),'hh:mm tt'), 'MM-DD-YYYY hh:mm tt') - Timestamp(Date(Date#(TFDDTE, 'YYYYMMDD'), 'MM/DD/YYYY') + time(Time#([TFDTME],'hhmmt'),'hh:mm tt'), 'MM-DD-YYYY hh:mm tt'),'hh:mm') as "Flight Duration"

View solution in original post

2 Replies
carlcimino
Creator II
Creator II
Author

carlcimino
Creator II
Creator II
Author

Solved it.  Hope this helps!  A little bit of additional date/time formatting required based on the source system data fields.  The stumbling block was the format on the time fields to take the A or P into consideration --> time(Time#([TFATME],'hhmmt'),'hh:mm tt')

, interval(Timestamp(Date(Date#(TFADTE, 'YYYYMMDD'), 'MM/DD/YYYY') + time(Time#([TFATME],'hhmmt'),'hh:mm tt'), 'MM-DD-YYYY hh:mm tt') - Timestamp(Date(Date#(TFDDTE, 'YYYYMMDD'), 'MM/DD/YYYY') + time(Time#([TFDTME],'hhmmt'),'hh:mm tt'), 'MM-DD-YYYY hh:mm tt'),'hh:mm') as "Flight Duration"

View solution in original post