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

Some examples here

http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"wahiggin" wrote in
message ...

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

Is there a way to change the NETWORKDAYS function to accommodate a 6 day
work week?


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

I am trying to combined a couple of formulas from posts that I received
and keep getting an error message. NETWORKDAYS doesn't work by itself
because I have one extra work day, Saturday. So I was trying to
combine this:

=NETWORKDAYS(A2,B2)+INT((B2-A2+WEEKDAY(A2))/7)

and this:

=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX(NETWORKDAYS(A2+1,B2-1,$D$1:$D$3),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2))))))

A1 = Start Time
B1 = End Time
A2 = Task Start Date/Time
B2 = Task End Date/Time
D range = Holidays (my exmaple only had three holidays so it was
D1:D3)

To make this:

=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX((NETWORKDAYS(A2+1,B2-1,$D$1:$D$3)+INT((B2-A2+WEEKDAY(A2))/7)),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2))))))

But I keep getting an error message.

Any ideas?


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

You are limited to 255 characters in a formula - you have 365 in your
final formula, so you'll have to approach it differently. Can you split
your formula and make use of helper cells? It strikes me that a UDF
might be a better approach here.

Hope this helps.

Pete

wahiggin wrote:
I am trying to combined a couple of formulas from posts that I received
and keep getting an error message. NETWORKDAYS doesn't work by itself
because I have one extra work day, Saturday. So I was trying to
combine this:

=NETWORKDAYS(A2,B2)+INT((B2-A2+WEEKDAY(A2))/7)

and this:

=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX(NETWORKDAYS(A2+1,B2-1,$D$1:$D$3),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2))))))

A1 = Start Time
B1 = End Time
A2 = Task Start Date/Time
B2 = Task End Date/Time
D range = Holidays (my exmaple only had three holidays so it was
D1:D3)

To make this:

=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX((NETWORKDAYS(A2+1,B2-1,$D$1:$D$3)+INT((B2-A2+WEEKDAY(A2))/7)),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2))))))

But I keep getting an error message.

Any ideas?


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

Whats a udf?


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

The limit is 1024 characters when measured in R1C1 notation--not 255.

Pete_UK wrote:

You are limited to 255 characters in a formula - you have 365 in your
final formula, so you'll have to approach it differently. Can you split
your formula and make use of helper cells? It strikes me that a UDF
might be a better approach here.

Hope this helps.

Pete

wahiggin wrote:
I am trying to combined a couple of formulas from posts that I received
and keep getting an error message. NETWORKDAYS doesn't work by itself
because I have one extra work day, Saturday. So I was trying to
combine this:

=NETWORKDAYS(A2,B2)+INT((B2-A2+WEEKDAY(A2))/7)

and this:

=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX(NETWORKDAYS(A2+1,B2-1,$D$1:$D$3),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2))))))

A1 = Start Time
B1 = End Time
A2 = Task Start Date/Time
B2 = Task End Date/Time
D range = Holidays (my exmaple only had three holidays so it was
D1:D3)

To make this:

=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX((NETWORKDAYS(A2+1,B2-1,$D$1:$D$3)+INT((B2-A2+WEEKDAY(A2))/7)),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2))))))

But I keep getting an error message.

Any ideas?


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

--

Dave Peterson