Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Calculating hours between 2 timestamps

Hi I have 2 fields Logged on and Closed on.

I want to calculate the number of hours between the 2 excluding weekends and the working day is only 7am to 5pm.

If a case is opened and closed outside of this time then i want it to return a 0.

Does any one know the correct formula for this?

thanks,

laura

1 Solution

Accepted Solutions
Not applicable

Re: Calculating hours between 2 timestamps

      Interval(

rangesum(

NetWorkDays([Logged On]+1,[Closed On]-1) * MakeTime(10)

,if(NetWorkDays([Closed On],[Closed On]),Rangemin(rangemax(frac([Closed On]),maketime(7)),maketime(17))-Rangemax(rangemin(frac([Closed On]),maketime(7)),maketime(7)),0)

,if(NetWorkDays([Logged On],[Logged On]),Rangemin(rangemax(frac([Logged On]),maketime(17)),maketime(17))-Rangemax(rangemin(frac([Logged On]),maketime(17)),maketime(7)),0)

,if(NetWorkDays([Logged On],[Logged On]) and floor([Logged On])=floor([Closed On]),-MakeTime(10))))as Status_WorkingHours_Cal,

3 Replies
MVP
MVP

Re: Calculating hours between 2 timestamps

Not applicable

Re: Calculating hours between 2 timestamps

      Interval(

rangesum(

NetWorkDays([Logged On]+1,[Closed On]-1) * MakeTime(10)

,if(NetWorkDays([Closed On],[Closed On]),Rangemin(rangemax(frac([Closed On]),maketime(7)),maketime(17))-Rangemax(rangemin(frac([Closed On]),maketime(7)),maketime(7)),0)

,if(NetWorkDays([Logged On],[Logged On]),Rangemin(rangemax(frac([Logged On]),maketime(17)),maketime(17))-Rangemax(rangemin(frac([Logged On]),maketime(17)),maketime(7)),0)

,if(NetWorkDays([Logged On],[Logged On]) and floor([Logged On])=floor([Closed On]),-MakeTime(10))))as Status_WorkingHours_Cal,

MVP
MVP

Re: Calculating hours between 2 timestamps

Yes, seems like you have already found the thread or a derivate.

Community Browser