Use two different fields to make a time stamp and then do the time between those time stamps

[sforster]

I'm having trouble calculating the amount of time between two points, but each of those points has two fields, here's the breakdown of the fields that I'm using

Date_IN = YYYYDDD (Julian Date field)

Time_IN = NNNNNN (should be in HH:MM:SS but is straight numbers in the data)

*combine* to make IN_TIMESTAMP

Date_OUT = YYYYDDD (Julian Date field)

Time_OUT = NNNNNN (should be in HH:MM:SS but is straight numbers in the data)

*combine* to make OUT_TIMESTAMP

What I need to do is is find the amount of time between the IN and OUT TIMESTAMPS from the data but everything i've tried hasn't worked correctly and I'm at a complete loss ..... any help would be greatly apprecaited.

[rwunderlich · Accepted solution]

Interval(
(MakeDate(left(Date_OUT, 4)) + Right(Date_OUT, 3)-1 + Time#(Time_OUT, 'hhmmss'))
-
(MakeDate(left(Date_IN, 4)) + Right(Date_IN, 3)-1 + Time#(Time_IN, 'hhmmss'))
,'d hh:mm:ss') 

If you don't want #days to display exclude the "d" from the interval mask.  Any full days will be included in hours eg

23 21:33:00  vs  573:33:00

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[sforster]

Rob - This worked perfectly!!  You're awesome, thank you!!!