Creating SLA report - need help w/ accounting for working hours and time zones

[tonyt2103]

I am trying to create an SLA (service level agreement) report. I have data that supports a date/time that something was requested and a date/time of completion all associated with 1 time zone. I need to be able to calculate the number of minutes between request to completion. Then take that number and subtract non-working minutes (weekends and minutes outside 9am through 6pm). Then I will need to associate this formula to each time zone as they relate to the data.

For example, if the report is showing in PST and those working hours are 9am to 6pm, then working hours for EST are 6am to 3PM. If the request for a rep on EST is on Monday at 6am PST and he completes it by Tuesday at 7am PST, that would be 25 total hours (or 1500 total minutes) but working hours would be 10 working hours (or 600 working minutes).

Another example: Same EST rep gets a request at 2pm PST on Friday and completes the request by Monday at 7am PST. This is 65 total hours (or 3900 total minutes) but really the SLA was 2 working hours or 120 working minutes (2pm to 3pm on Friday since he is on EST, and then 6am to 7am).

Please let me know if there is any idea of how to construct this formula via excel 2007. I'm happy to clarify or elaborate anything you have questions about. Thank you!