Exclude Specific Hours in weekends - Time Calculation

[Anonymous]

Dear Experts,

Kindly refer the attachment.

I want to calculate the time taken for the process flow.ie.Time taken from "In Queue" to "Complete".But here I want to exclude the hours from Friday 7PM to Monday 8AM.

I tried the networkdays,but it is not giving the expected answer since I need specific hours only.

Could you please help me

Thanks & Regards

Jeba

[Anonymous]

Experts,could you please help me in this logic

[Anonymous]

Experts,could you please help me in this logic

[tresesco]

Could you explain your requirement a bit more with expected output against a sample data?

[Anonymous]

Hi Tresesco,

Thanks a lot for your reply.

Attaching the qlikview application I have got previously which was working perfectly except this scenario.

Also attaching the updated excel file with some more details.

Kindly refer the highlighted rows.

In this scenario,Team 3 has started their work on 13.06.14 at 17.52 Hours and they completed that work on 16.06.14 at 12 Hours.If we directly calculate the hours between it will come at 66.08 Hours which is not correct.I want to exclude the hours from Friday 7 PM to Monday 8AM.so actual time taken was 6.08 Hours.The networkdays function is not giving the correct solution,because I have specific times.

May I request you to help me.

Thanks & Regards

Jeba

[tresesco]

I would follow this: Re: Calculate hours between two Date/Time strings

[Anonymous]

Hi Tresesco,

As per this link they have used the static inline table,but my data is not static and the code has to work for all the weekends moving forward.

Could you please help me

Thanks & Regards

Jeba

[tresesco]

Dear Jeba,

The inline data in the post is being used as input data for instance, it has not necessarily to be so, you can take your loaded data. Please go through the post patiently, I am sure, it would be able to direct you to your goal.

[#9]

Hi,

Can the difference between Complete Date and InQueue  Date be more than one week ??

if not then you can use formula like this

=if(WeekDay(Timestamp#(InQueue Date,'MM/DD/YYYY hh:mm'))>WeekDay(Timestamp#(Complete Date,'MM/DD/YYYY hh:mm')),interval(Interval(Timestamp#(Complete Date,'MM/DD/YYYY hh:mm')-Timestamp#(InQueue Date,'MM/DD/YYYY hh:mm'),'hh:mm')-timestamp('60:00','hh:mm'),'hh:mm'),Interval(Timestamp#(Complete Date,'MM/DD/YYYY hh:mm')-Timestamp#(InQueue Date,'MM/DD/YYYY hh:mm'),'hh:mm'))

[Anonymous]

Hi Vivek,

Its not predicatable.

I have seen many cases which is more than one week/month

Thanks & Regards

Jeba