A challenge for an Excel Master...

[wahiggin]

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

 

[Peo Sjoblom]

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]

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

 

[wahiggin]

I am trying to combined a couple of formulas from posts that I receive
and keep getting an error message. NETWORKDAYS doesn't work by itsel
because I have one extra work day, Saturday. So I was trying t
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 wa
D13)

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

 

[Pete_UK]

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]

Whats a udf?

 

[Pete_UK]

UDF stands for a User Defined Function. This is one which you write
yourself in VBA and can use from your own worksheet as if it were one
of Excel's functions. Let's say you had a UDF called
"Calc_time(cell_ref1, cell_ref2 )", then you would enter this formula
in a cell:

=Calc_time(A1,B1)

and it would return the result of whatever it calculates.

Hope this explains.

Pete

 

[Dave Peterson]

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

 

[Pete_UK]

Well, Dave, would you like to translate that into R1C1 notation? <bg>

Pete

 

[Dave Peterson]

I'd put the formula in a cell and change tools|options|general tab|and check
R1C1 Reference style.

(or use ConvertFormula in code)

Well, Dave, would you like to translate that into R1C1 notation? <bg>

Pete