Average Time for Specific Item

[stkinkuwait]

Thanks in advance for any help!

I am trying to calculate the average time it takes to accomplish a specific
task amongst many tasks during an 8 hour weekday, excluding holidays. Work
day is from 8 AM to 4 PM.

Model Date Received Date finished
Ford 10/17/07 8:30 AM 10/18/07 12:00 PM
Chevy 10/17/07 8:00 AM 10/17/07 12:00 PM
Ford 10/17/07 8:00 AM 10/17/07 12:00 PM
Chevy 10/17/07 8:40 AM 10/17/07 12:00 PM
Ford 10/18/07 9:41 AM 10/19/07 12:00 PM
Dodge 10/18/07 9:15 AM 10/18/07 12:00 PM


What formula would allow me to find out the average time it takes to process
a Ford?

Thanks again!

 

[stkinkuwait]

The formula I am using is returning a #NUM! error.

=IF(A:A=Ford,TEXT(C:C-B:B,"hh:mm"),0)

Of course this is way above my user level too so the error is between the
seat back and the keyboard.

 

[stkinkuwait]

OK fixed the #NUM! error, forgot the "" around Ford. How do I configure it
to count just the 8 hour work day, minus weekends and holidays?

 

[Mike H]

Hi,

I can't get this into a single cell but here's one way

Put this formula in d2 and drag down to calculate the hours. You will need
to create a named range called 'Holidays' for holiday dates.

=((NETWORKDAYS(B2,C2,Holidays)-1)*("16:00"-"08:00")+MOD(C2,1)-MOD(B2,1))*24

The work out the average with this array formula (See below)

=AVERAGE(IF(A2:A7="Ford",D27))

Which for your posted data is 8.605555556 Hours

If you want hours and mins change the formula to

=AVERAGE(IF(A2:A7="Ford",D27))*24
and format as time to get 08:36:20

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[stkinkuwait]

I can't seem to get it to calculate the NETWORKDAYS at all. Even if I strip
the formula down to just:

=NETWORKDAYS(B2,C2,E2)

I still get a #NAME? error. Using 2003 btw.

 

[Mike H]

Apologies,

I should have pointed out that Networkdays is part of the analysis toolpak, so

Tools|Addins and select "Analysis toolpak"

Mike

 

[David Biddulph]

And have you dome what Excel help for the NETWORKDAYS function tells you to
do?

 

[stkinkuwait]

Thanks again for the help. I know we are getting close to the solution here.

This is the result:

Model Time Received Date Finished Time
Ford 10/17/07 8:30 AM 10/18/07 12:00 PM 12:00:00
Chevy 10/17/07 8:00 AM 10/17/07 12:00 PM 0:00:00
Ford 10/17/07 8:00 AM 10/17/07 12:00 PM 0:00:00
Chevy 10/17/07 8:40 AM 10/17/07 12:00 PM 8:00:00
Ford 10/18/07 9:41 AM 10/19/07 12:00 PM 7:36:00
Dodge 10/18/07 9:15 AM 10/18/07 12:00 PM 18:00:00

Which results in incorrect average times by default:

Ford Chevy Dodge
12:48:00 0:00:00 0:00:00


Even if I remove holiday days from the equasion it doesn't change the
numbers so thats not where the error is occuring in calculation.

 

[stkinkuwait]

In fact removing ("16:00"-"08:00") from the equasion has no affect on the
result at all. Neither does altering those times to reduce or increase the
result. This appears to be the source of my woes.

 

[Mike H]

Hi,

Several things

1. Removing 08:00 - 16:00 doesn't change anything!!
Yes it does. The correct way to remove it is this
=((NETWORKDAYS(B2,C2,Holidays)-1)+MOD(C2,1)-MOD(B2,1))*24

Now for your first set of times

Ford 10/17/07 8:30 AM 10/18/07 12:00 PM 12:00:00

The formula returns 27.5 hours
15.5 hours on 10/17 and 12 hours the next day
put it back
=((NETWORKDAYS(B2,C2,Holidays)-1)*("16:00"-"08:00")+MOD(C2,1)-MOD(B2,1))*24

and we get 11.5 hours. 7.5 hours on 10/17 and 4 hours the bext day

2. this line
Chevy 10/17/07 8:00 AM 10/17/07 12:00 PM 0:00:00

You note is returning zero so in which case the times aren't correctly
formatted dates and times OR it's the result of formatting as time. Format as
general and you should see 4.0

3. Your first line returns 12:00:00 and this is because you have formatted
as time. Format as GENERAL

Mike

 

[stkinkuwait]

I fixed the formatting but it still isn't returning the value's you are
getting. I did find another way of skinning the cat though.

Your Formula:
=((NETWORKDAYS(B2,C2,E10)-1)*("16:00"-"08:00")+MOD(C2,1)-MOD(B2,1))*24

Result:

Time
276:00:00
96:00:00
96:00:00
80:00:00
295:36:00
258:00:00

My Formula:
=(NETWORKDAYS(B2,C2,E2:E7)-1)*("16:00"-"08:00")+TEXT(C2-B2,"hh:mm")

Result:
Time
11:30:00
4:00:00
4:00:00
3:20:00
12:19:00
10:45:00


My way seems to hold up to everything I have thrown at it. In the interest
of learning I am curious as to what might be the problem with your's and if
you think there may be future problems with mine?