sorry, I didn't understand your date structure

While the weeknum() function returns the weeknum in the year it doesn't
appear to work in an array function. If you create helper columns (in my
example columns A and E) using weeknum(a1) to get the week number for each
entry, then the formula below will create the pattern you are looking for

=SUMPRODUCT(($A$1:$A$5=$A10)*(B$7=($C$1:$C$5))*(B $7<=($E$1:$E$5))*1)

Hope this helps
Lance


"PR" wrote:

Lance,
I do not understand what you mean when you say "if the first two digits are
the weeks"

Paul

"LanceB" wrote in message
...
Im not sure I understand your dates, however if the first two digits are
the
weeks

1 2 3 4 5 6 7 8 9 10
p1 1 1 1 1 1 1 1 1 1 1
p2 0 0 0 0 0 0 0 0 0 0
p3 0 0 0 0 0 0 1 1 1 1
p4 0 0 0 0 0 0 0 0 0 1
p5 0 0 0 0 0 0 0 0 0 0

=SUMPRODUCT(($A$1:$A$5=$A8)*(B$7=--MID($B$1:$B$5,1,2))*(B$7<=--MID($D$1:$D$5,1,2))*1)



"PR" wrote:

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