Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Date Diffrence in Hours Mins And Sec

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,

14 Replies
PrashantSangle

Hi,

Interval(YourExpression, format)

please check below thread for detail

http://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/FormattingFunctions/Int...

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sujeetsingh
Master III
Master III

Interval is the solution for your need.

avinashelite

interval(expression [ , format-code ])

The interval function formats the expression as a time interval according to the string given as a format-code. If the format code is omitted, the time format set in the operating system is used. Intervals may be formatted as a time, as days or as a combination of days, hours, minutes, seconds and fractions of seconds.

You are using two arguments, so QV tries to interprete your second argument as format code (which must be a format code string).

If you want to get the difference in days, you need to use a subtraction of your counts (if counting is correct here)

Anonymous
Not applicable
Author

Please don't mark as a Correct Answer before my response on your reply.

Anonymous
Not applicable
Author

Please help me swuehl

avinashelite

Don't worry only the community moderate has that rights others can't mark it , except you

Digvijay_Singh

No one can do that except you, AFAIK only creator of the discussion is allowed to mark correct answer

Anonymous
Not applicable
Author

i am not marked as a Correct Answer

sasiparupudi1
Master III
Master III

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')),'hh:mm:ss')

hth

Sasi