OK. Still using the original sheet, do the following
In cell D1 put the text Department. Define cells D2:D6 with the name DEPT
Put the Department names in these cells.

Now go further down the sheet, where the main table is. There is a cell with
the word WEEKLY in it. On my sheet this is in cell A14. In Cell A13 put the
name of the department that you wish to summarise. Eg Department A.

Next go to the main body of the table, the first cell to the right of the
first date. This is cell B15 in my sheet.

Change the formula in this cell. Change it to the following:
=SUMPRODUCT((START<=$A15+B$13)*(END$A15+B$13)*(DE PT=$A$13))

Copy right to cell H15 and copy down as far as necessary.

Now, every time you wish to see a different department, change the value in
A13 to the department that you want to see.

Regards
Paul Allen

"PR" wrote in message
...
Paul,
I have done that and it works great.

I would like to add one more thing to this if possible, how can I filter
on
departments, so that the numbers change so that it reflects just the
department I have chosen.

Paul


"Paul Allen" wrote in message
...
Ok. Do this first:
Put headings in as follows A1 - Name , B1 - Start (Date), B2 - End
(Date)

Next, using your data p1-p5 in cells A2 to C6. Define B2:B6 with the
name
START, and define cells C2:C6 with the name END.

Then follow the previous instructions from the line
So instead, use this solution:

This should work.

Regards
Paul Allen


"PR" wrote in message
...
Paul,
I have followed you instructions from the part you say 's instead, use
this
solution' but I get an error, #Name?

In the first part of your answer you say to put headings, but in the
second
part you do not, is this way it does not work.

Paul

"Paul Allen" wrote in message
...
I had a similar problem to this myself, however it was for hours
worked
and
not weeks. The principle is the same though.

Put headings in as follows A1 - Name , B1 - Start (Date), B2 - End
(Date)

Next, using your data p1-p5 in cells A2 to C6. Define B2:B6 with the
name
START, and define cells C2:C6 with the name END.

Put 1/1/2004 into cell A8 and in cell B8 the formula =A8+1, copy this
as
far
right as desired.

The formula in cell A9 should be as follows:
=SUMPRODUCT((START<=A$8)*(ENDA$8))
Copy this as far right as desired. This will then show in Row 9 the
TOTAL
number of persons employed on each particular day. It will not show
the
breakdown figures, just the desired totals.

This solution however will not show you by the week and as pointed
out
elsewhere will mean you'll run out of columns eventually.

So instead, use this solution:

In cells B13:H13 enter the figures 0, 1, 2, 3, 4, 5, 6
In cells B14:H14 the text MON, TUE, WED, THU, FRI, SAT, SUN
In A15 enter 29/12/2004, and in A16 the formula =A15+7, copy down as
necessary

In cell B15 enter the formula below (Note placement of $s)

=SUMPRODUCT((START<=$A15+B$13)*(END$A15+B$13))

Copy this across and down to H28, it can be copied further down as
necessary. You will now see a table of the total persons employed on
any
given day. Finally to see the total weekly figures add a last column
on
the
right. In I 14 enter "TOTAL"

I 15 should have the formula =MAX(B15:H15)
Copy this down to the end of the table.

Using your data you will get something like this:

0 1 2 3 4 5 6
WEEKLY M T W T F S S TOTAL
29/12/2003 0 0 0 1 1 1 1 1
05/01/2004 1 1 2 2 2 3 3 3
12/01/2004 4 4 4 4 4 4 4 4
19/01/2004 3 3 4 4 4 3 2 4
26/01/2004 2 2 2 2 2 1 0 2
02/02/2004 0 0 0 0 0 0 0 0
09/02/2004 0 0 0 0 0 0 0 0
16/02/2004 0 0 0 0 0 0 0 0
23/02/2004 0 0 0 0 0 0 0 0
01/03/2004 0 0 0 0 0 0 0 0
08/03/2004 0 0 0 0 0 0 0 0
15/03/2004 0 0 0 0 0 0 0 0
22/03/2004 0 0 0 0 0 0 0 0
29/03/2004 0 0 0 0 0 0 0 0

The only thing to bear in mind is that as other people are added they
will
be out of the defined range name. The way round this is to use Insert
Row
somewhere in the middle of the range name.

This is my first post to the group. I have tried to be as clear and
concise
as possible. I hope I have helped in some way.

Paul Allen

"PR" wrote in message
...
I am looking for a way to create a chart over a year period by each
week,
to
let me now how many people where in my organisation over a year
period,
The
problem I have is that I only have a start and end date. i.e.

p1 01/01/04 - 31/01/04
p2 21/01/04 - 01/02/04
p3 07/01/04 - 24/01/04
p4 10/01/04 - 25/01/04
p5 12/01/04 - 19/01/04

I would like to see a table like this to create a chart.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
25
26
27
28 29 30 31
p1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1
p2 1 1 1 1 1 1 1 1 1 1
1
p3 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
p4 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
p5 1 1 1 1 1 1 1 1
1 1 1 1 1 1 2 2 2 3 3 4 4 4 4 4 4 4 4 3 4 4 4 4 3 2 2 2 2 2 2


can anyone help.

Paul