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