There may be multiple start and end dates that
overlap within each work center.

Well, that right there is going to be your "killer".

If you want to "chart" the hours you'd have do it separately for each
instance of a work center.

At this point what you want to do *is a lot of work*. I'm just a volunteer
(if you get my drift).


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I've added a column for average hours and modified the formula slightly so
that I don't get the (#DIV/O!) error. I entered it like this,
=D3/(NETWORKDAYS(A3,B3)-0).

I would like to be able to pull the average hours over and have them fill
in
my spreadsheet under the dates they are being divided by (start & End
dates)

On Sheet2 I have all the work centers listed in column A and starting in
column C row 1, I have the dates running sequentially, starting on 3/2/09
and
running through the end of the year.

The formula would need to be able to take the available hours on a given
work center from Sheet1and split them up equally between the start and end
date (columns A & B on Sheet1). Then put those hours on the spreadsheet
under
the correct work center and under the dates listed on Sheet2 row 1.

Column A Column B Column C Column D
Column E
Row1 Start Date End Date work center total hours
average hrs
row 2 3/2/09 3/6/09 18 bench 10
2

So on my spreadsheet it would show 2 hours under columns C (3/2/09)
through
column G (3/6/09) it would show 2 hours for each day in the row that has
work
center (18 bench). There may be multiple start and end dates that overlap
within each work center.

I hope you can help with this!

Thanks,
--
SRC


"T. Valko" wrote:

Instead of doing all that in one formula why not just add another column
to
your table?

Column E = Avg Hrs

The formula would be:

=D3/(NETWORKDAYS(A3,B3)-1)

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
That worked, thanks a lot, your awesome!

While were on a roll maybe you would know how to make this work.

Can that same formula be modified so it splits the hours up equally
from
start to end date? The formula currently puts the total number of hours
in
the date column based on the start date that was shown in column B.

column A Column B Column C Column D
Row 1 Start date End date work center total hours
Row 2 3/2/09 3/4/09 18 Bench 10.0
Row 3 3/4/09 3/12/09 62 Haas 100.0
Row 4 3/5/09 3/12/090 SAW 12.0

Currently when you look at the work load on 62 Haas it would show you
that
on 3/4/09 there is 100.0 hours of work. I would like to have it broken
up
by
the number of work days between 3/4/09 and 3/12/09. So it would take 6
working days (not counting weekends) and then divide the number of
hours
into
those 6 days so the schedule would show 16.66 hours each day starting
3/4/09
and ending 3/12/09.

Thanks,







--
SRC


"T. Valko" wrote:

Ok, try this on Sheet2...

B2:F2 = dates
A3:A8 = work centers

Enter this formula in B3:

=SUMPRODUCT(--(Sheet1!$B$2:$B$150=B$2),--(Sheet1!$D$2:$D$150=$A3),Sheet1!$J$2:$J$150)

Copy across to F3 then down to row 8.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
All this date is on Sheet1,
Column B shows the start date
Column C shows the end date
Column D has the work center
Column J has the hours
The other columns on Sheet1 are not coming into play.

The chart showing this schedule is on Sheet2.

Column A shows all the work centers on Sheet2
and row 2 has all the work days listed

--
SRC


"T. Valko" wrote:

I'm having a hard time trying to picture how your data is setup.

Column D = work center
Column J = hours worked

Where's the date?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thanks, That solved problem #1.

For problem #2.
The hours I'm looking for are kept in column J. So in the column
that
has
the work date, I'm trying to see if there are any hours from
column
J
for
the
specific work center listed in column D.
I hope that makes sense.
Thanks in advance!
--
SRC


"T. Valko" wrote:

=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<" 3/2/2009"),$J$2:$J$150))

Try this normally entered formula.

Use a cell to hold your date criteria:

A1 = 3/2/2009

=SUMPRODUCT(--(D2:D150=Sheet2!A3),--(B2:B150<A1),J2:J150)

Problem #2 = Where do we lookk for the hours worked?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Problem #1
I am also trying to find out the number of LATE hours by work
center.
So
if
the work center has some hours logged in but there less than a
given
date
i
want to see them. The formula I'm trying to use is.
=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<"3/2/2009"),$J$2:$J$150)).
This formula does not give me the correct results. column B
has
the
dates,
column D has the work centers and column J has the
hours.
Problem #2
I have entered the dates excluding weekends at the top of each
column.
I
have the work centers listed in the rows going down the page.
I
want
to
be
able to see the number of hours by work center by date.

using excel 2003
--
SRC