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!

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" wrote:

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!

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?

"stkinkuwait" wrote:

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" wrote:

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!

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",D2:D7))

Which for your posted data is 8.605555556 Hours

If you want hours and mins change the formula to

=AVERAGE(IF(A2:A7="Ford",D2:D7))*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" wrote:

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" wrote:

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!

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" wrote:

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",D2:D7))

Which for your posted data is 8.605555556 Hours

If you want hours and mins change the formula to

=AVERAGE(IF(A2:A7="Ford",D2:D7))*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" wrote:

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" wrote:

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!

Apologies,

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

Tools|Addins and select "Analysis toolpak"

Mike

"stkinkuwait" wrote:

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" wrote:

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",D2:D7))

Which for your posted data is 8.605555556 Hours

If you want hours and mins change the formula to

=AVERAGE(IF(A2:A7="Ford",D2:D7))*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" wrote:

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" wrote:

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!

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.

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

"stkinkuwait" wrote in message
...
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" wrote:

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",D2:D7))

Which for your posted data is 8.605555556 Hours

If you want hours and mins change the formula to

=AVERAGE(IF(A2:A7="Ford",D2:D7))*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" wrote:

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" wrote:

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!