Help needed with times please

[timsaddler]

hello
Can someone advise how i work out the elapsed time from this data

A.collect_finish

20140214 10:56:00

B.deliver

20140214 12:41:17

I first strip the time "right(collect_finish,8)" out to give

10:56:00

then "right(deliver_start,8)" out to give

12:41:17

interval(right(collect_finish,8),right(deliver_start,8)) as #ActTravelTime,

This returns 1.8222 which i guess is hours - converted = 109.333 mins?

Answer should be 105 mins 17 secs

[tresesco]

Have you tried like :

Interval(time1-time2 , 'mm') as MinDiff

?

[MK_QSL · Accepted solution]

=INTERVAL(TIME(Timestamp#('20140214 12:41:17','YYYYMMDD hh:mm:ss')) -

TIME(Timestamp#('20140214 10:56:00','YYYYMMDD hh:mm:ss'))

,'mm:ss')

[jonathandienst]

Hi

This should do it:

Interval(TimeStamp#(collect_finish, 'YYYYMMDD hh:mm:ss') - TimeStamp#(deliver_start, 'YYYYMMDD hh:mm:ss'), 'hh:mm:ss')

HTH

Jonathan

[MarcoWedel]

=Interval(Timestamp#('20140214 12:41:17', 'YYYYMMDD hh:mm:ss')-Timestamp#('20140214 10:56:00', 'YYYYMMDD hh:mm:ss'), 'mm:ss')

regards

Marco

[#6]

Data:

LOAD * INLINE [

Collect,Deliver

"20140214 10:56:00","20140214 12:41:17"

];

DataFinal:

LOAD

  Interval(Deliver - Collect,'hh:mm:ss')*24*60 as TimeDiffNumberFormat,

  Interval(Deliver - Collect,'hh:mm:ss') as TimeDiffTimeFormat

RESIDENT Data;

DROP TABLE Data;

[timsaddler]

Thanks guys for your prompt response - saved me hours messing about...