Calculate only working hours between two dates with several constraints

Hi all,


I have read many threads regarding the calculation of working hours between two dates, but couldn't file a solution that fits my problem.
I am facing a difficult calculation as there are many constraints.

I need to calculate the time elapsed between two tickets raised. A client raises a ticket, at any time and any day, and our staff resolves the ticket within our working hours.
I then need to calculate the time it takes to resolve the ticket within the working hours, including the weekend, and excluding bank holidays. If a ticket is resolved outside of working hours and days, only the time WITHIN the working hours and days should be counted.

I have found great answers that either include or exclude weekend. However, I have 3 major constraints that make all the formulas and solutions proposed unusable:

• I manage several locations, each location has different working hours and different working days. E.g. NYC is open from Monday to Sunday, with different hours for each day. Los Angeles only Monday to Friday. New Orleans Monday to Saturday. Again, the working hours are different each day (e.g. Monday to Friday 5am 730pm, Saturday 6am - 2pm, Sunday 9am - 5pm).
• Each location has different Bank Holidays because they're located in different states (and sometimes countries).
• Some of our locations have working hours that go late into the night (e.g. 5am to 1am).

I have tried the NETWORKDAYS.INTL for each day and summing them but this hasn't work.
Also, I cannot seem to calculate properly working hours when they extend to the day after (e.g. office open from 5am Tuesday to 1am Wednesday).

I have attached the file I am using. Tab 1 is for calculation. Tab 2 is the working hours for each location/day, Tab 3 is the list of Bank Holidays.

I have spent days on this, I cannot wrap my head around this problem.

Please help me.

Cheers,
Jill

Hi FlameRetired,

Thanks a lot for your answers. It gives me a bit of hope.
Basically a ticket can be raised at anytime or anyday by a client. So it can be raised outside of working hours. It ALSO can be resolved at anytime by our staff, during "overtime". However, since we have "official business hours", only the time elapsed WITHIN the official working hours (Tab 2) need to be measured.

So in LA our working hours are only from Monday to Friday, however a client can raise a ticket on Saturday, and our "overtime" staff can resolve it also on Saturday. In this case, the time elapsed would be zero (0), because it is both raised and solved outside of "official business hours."

Am I expressing myself correctly? I can try to explain it more clearly if necessary.

Thanks a lot FlameRetired!

Originally Posted by FlameRetired

Thank you for clarifying that.

Those factors make it even more challenging. Are you open to lots of helper columns/rows?

Yes, without any doubt. I just want to solve this problem. Any help is immensely appreciated.

Thanks Dave.

Hey Dave,

Yes that could be helpful if you uploaded that file.
Thanks