how to calculate time spent excluding non-business hours

[#1]

Hi All

I'm trying to do some time spent calculations but I want to exclude non-business hours. I'm not sure where I should get started.  Should I build a table of business hours or a table of non-business hours?  Then how do I do the necessary calculations.

For example, I have a ticket that was created on 10/05/2011 at 9:00am.  I didn't close the ticket until 10/06/2011 at 10:00am.  My business hours are 8 to 5pm Monday to Friday.  Therefore, my time spent will be 10 hours since we don't care to exclude lunch or break-times.

Any suggestions will be most appreciated.

Best Regards,

Jack

[matt_crowther]

Jack,

There may be a more elegant way of achieving this but below is a principal that should see you on your way:

Lets say the 'Ticket_Start_Time' is 13:00 on a 'Ticket_Start_Date' of 3rd Oct with the end Data / Time 11:00 on the 5th. Assuming a working day of 8:00 - 18:00 (10hrs) then the basis of a soution is:

(18:00 - Ticket_Start_Time) + (Ticket_End_Time - 8:00) + (10x((Ticket_End_Date - Ticket_Start_Date)-1))

That gives a result of 18hrs. The first part works how many hours to the end of the 1st day, the second how many on the last day and the final section the number of complete days between the start and end times.

Obviously this will need to be adapted slightly to account for start and end times within the same working day etc and Qlikview will need to recognize the times as times but the general principal is there.

All the best,

Matt - Visual Analytics Ltd

[swuehl]

Hi Jack,

maybe like suggested in this thread? Solution in last post looks quite nice:

http://community.qlik.com/thread/25076

Hope this helps,

Stefan