Date Diffrence in Hours Mins And Sec - Page 2 - Qlik Community

Anonymous · ‎2016-06-08

Hi,

I have a date format like

[Create Time Release] [Release Time])

2015-12-17 09:52:24 AM 2015-12-17 09:58:22 AM

I used following script to find the diffrence in HH:MM:SS, But its not working for me.

Interval(

rangesum(

NetWorkDays([Create Time Release] +1,[Release Time]-1) * MakeTime(9) // 8 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays([Release Time],[Release Time]),Rangemin(rangemax(frac([Release Time]),maketime(7,30)),maketime(16,30))-Rangemax(rangemin(frac([Release Time]),maketime(7,30)),maketime(7,30)),0) // working hours last day

,if(NetWorkDays([Create Time Release] ,[Create Time Release] ),Rangemin(rangemax(frac([Create Time Release] ),maketime(16,30)),maketime(16,30))-Rangemax(rangemin(frac([Create Time Release] ),maketime(16,30)),maketime(7,30)),0) // working first day

,if(NetWorkDays([Create Time Release] ,[Create Time Release] ) and floor([Create Time Release] )=floor([Release Time]),-MakeTime(7,30)) // correct for first equals last day

)

as DurationRelease,

working hours 7:30 to 4:30 and used TimestampFormat= 'YYYY-MM-DD hh:mm:ss[.fff] TT';

Thanks,

Anil_Babu_Samineni · ‎2016-06-08

As follow Sasidhar

Might be like this

interval(timestamp(timestamp#('2015-12-17 09:58:22 AM','YYYY-MM-DD hh:mm:ss TT'))-timestamp(timestamp#('2015-12-17 09:52:24 AM','YYYY-MM-DD hh:mm:ss TT')),'YYYY-MM-DD hh:mm:ss TT')

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

jonathandienst · ‎2016-06-08

In the simplest form:

Interval([Release Time] - [Create Time Release], 'hh:mm:ss') as DurationRelease

if you have loaded the two fields as proper QV numeric date/time fields. If thay are still string values, then you will need

Interval(Timestamp#([Release Time], 'yyyy-MM-dd hh:mm:ss TT') - Timestamp#([Create Time Release], 'yyyy-MM-dd hh:mm:ss TT'), 'hh:mm:ss') as DurationRelease

Although I recommend that the Timestamp#()' s are in your load script so that you are storing date/time values and you can use the simple expression above.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

jonathandienst · ‎2016-06-08

Your expression could be rendered:

Interval(

Rangesum(

NetWorkDays([Create Time Release] + 1, [Release Time] - 1) * 9/24,

If(NetWorkDays([Release Time], [Release Time]),

Rangemin(Rangemax(frac([Release Time]),maketime(7,30)),maketime(16,30)) -

Rangemax(Rangemin(frac([Release Time]),maketime(7,30)),maketime(7,30)), 0),

If(NetWorkDays([Create Time Release] ,[Create Time Release]),

Rangemin(Rangemax(frac([Create Time Release]),maketime(16,30)),maketime(16,30)) -

Rangemax(rangemin(frac([Create Time Release]),maketime(16,30)),maketime(7,30)), 0),

If(NetWorkDays([Create Time Release],[Create Time Release])

And floor([Create Time Release]) = floor([Release Time]),

-MakeTime(7,30))

)

'hh:mm:ss') as DurationRelease,

This is syntactically correct, but I am not sure if is logically correct as well.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

ThornOfCrowns · ‎2016-06-08

If one of the answers is correct, please mark it as so.

swuehl · ‎2016-06-08

Do you still have issues with that expression (which I remember quite well, even if it's some years old)?

If so, it would be really helpful if you actually would describe your issues in details, giving some sample input records, returned results from the expression and the results you expect to get instead.

From what I can see for now, you have a 9 hour working day, so the correction for same start and end date should be