Working out business hours from/to specific dates/times

[flejbeetle]

Hello!

I am hoping someone can assist with a template they just happen to have kicking around...

I need to know the specific business hours from the first date/time to the second date/time, based on:

• a 9 hour (8-5pm) day
• a 5 day working week (Mon-Fri),
• with BC, Canada holiday dates factored in.

Here is an example of the data I am working with -

MASTER CALL OPEN TIME (cell format = date):
2013-01-02 12:58:00 PM

EVENT 1 OPEN TIME (cell format = general):
Jan 3 2013 11:15AM

Notes:
- There are 2 different applications this data is being pulled from that are limited in export functionality so these cell formats seem to be the only way to get the information into excel.
- I receive thousands of lines of these events every month so manually working out this information is not a preferably option.

Cheers,

Scott.