I am struggling to find a formula to calculate exact time between two
date/time values excluding out-of-office times. Let me explain:
I have two dates, the date/time a task was assigned to a department to
be worked and the date/time the work was completed by the department.
This department has set office hours during the Mon-Fri workweek and
then different office hours on Sat and they do not work on Sun. I am
trying to create a formula where the time they are not in the office
does not count against them for a true completion time. I would like
the formula to be dynamic so that if the office hours change I can
simply put in new office hours at cells on a different worksheet.

Start date/time = 5/2/2006 16:15
Completed date/time = 5/8/2006 15:59
Simple subtraction = 5.988888889
Manual calculation = 2.447222222

Mon-Fri Office hours = 7:00am - 7:00pm
Sat Office hours = 7:00am - 6:00pm

They way I did the manaul calculation is like this:
5/2/2006 16:15 5/8/2006 15:59 5.988888889 Total Time
5/2/2006 19:00 5/3/2006 7:00 0.5 Out of Office
5/3/2006 18:00 5/5/2006 7:00 1.541666667 Out of Office
5/5/2006 19:00 5/6/2006 7:00 0.5 Out of Office
5/6/2006 19:00 5/7/2006 7:00 0.5 Out of Office
5/7/2006 19:00 5/8/2006 7:00 0.5 Out of Office
2.447222222 Subtract Total Time from Out of Office times.

Here is some sample data from my report of thousands of tickets
compelted by this department:

Created Date Closed Date

5/2/2006 16:15 5/8/2006 15:59
5/3/2006 9:45 5/10/2006 8:10
5/3/2006 16:00 5/5/2006 16:34
5/4/2006 8:47 5/8/2006 15:25
5/4/2006 15:30 5/5/2006 16:33
5/4/2006 16:00 5/5/2006 16:41
5/5/2006 17:45 5/8/2006 9:33
5/6/2006 14:45 5/8/2006 9:16
5/6/2006 15:00 5/8/2006 9:24
5/6/2006 15:15 5/8/2006 9:51
5/6/2006 15:45 5/8/2006 9:39
5/6/2006 15:45 5/8/2006 10:19
5/6/2006 16:00 5/8/2006 9:45
5/6/2006 16:00 5/8/2006 9:46
5/7/2006 10:58 5/8/2006 15:22
5/7/2006 10:58 5/8/2006 16:57
5/9/2006 13:45 5/17/2006 15:08
5/10/2006 10:59 5/11/2006 11:18
5/11/2006 13:48 5/12/2006 17:08
5/12/2006 11:15 5/13/2006 13:38
5/12/2006 11:15 5/15/2006 10:10
5/12/2006 11:15 5/15/2006 10:20
5/12/2006 11:31 5/13/2006 13:50
5/12/2006 11:31 5/13/2006 14:10

Has someone done this before, or is smart enough to create a formula
that will do it for me????

Wesley


--
wahiggin
------------------------------------------------------------------------
wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013
View this thread: http://www.excelforum.com/showthread...hreadid=547509