Forulma Needed

PR - What is the significance of the 1,2,3,4,5.......31 across the top? Are
those days, and if so how far out do they go, because you obviously cannot chart
a full year that way as you only have 256 columns.

Assuming they are weeks then I would suggest perhaps putting real dates in there
as opposed to those week numbers, and further assuming that you do this, I'll
assume that those dates are in say E1:BF1, your names/IDs are in say B2:B200,
your start dates are in C2:C200 and finish dates in D2200.

Now in cell E2 put the following formula and copy down and across to the whole
range E2:BF200

=IF(AND(E$1=$C2,E$1=$D2),1,"")

Done.

Obviously for those dates you can just put the first day of your year in E1 and
then in F1 put =E1+7 and copy across.

If you prefer chart type data then format all the cells with a white font and
then use conditional formatting to colour the pattern of any cell that contains
a 1. You will still be able to do the maths at the bottom of the sheet to get
the total numbers on a weekly basis.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004

"Ken Wright" wrote...
You are correct in that WEEKNUM is a PITA function that does not work
in an array. :-(
....

So improvise. If x were an array and you wanted what should be the array
result from WEEKNUM(x), try

=1+INT((x-DATE(YEAR(x),1,1)+WEEKDAY(DATE(YEAR(x),1,1),2))/7)

Ken,
There are weeks days across the top, it could be weeks, but how can I say a
person has been employed for that week?

Paul


"Ken Wright" wrote in message
...
PR - What is the significance of the 1,2,3,4,5.......31 across the top?
Are those days, and if so how far out do they go, because you obviously
cannot chart a full year that way as you only have 256 columns.

Assuming they are weeks then I would suggest perhaps putting real dates in
there as opposed to those week numbers, and further assuming that you do
this, I'll assume that those dates are in say E1:BF1, your names/IDs are
in say B2:B200, your start dates are in C2:C200 and finish dates in
D2200.

Now in cell E2 put the following formula and copy down and across to the
whole range E2:BF200

=IF(AND(E$1=$C2,E$1=$D2),1,"")

Done.

Obviously for those dates you can just put the first day of your year in
E1 and then in F1 put =E1+7 and copy across.

If you prefer chart type data then format all the cells with a white font
and then use conditional formatting to colour the pattern of any cell that
contains a 1. You will still be able to do the maths at the bottom of the
sheet to get the total numbers on a weekly basis.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004

LOL - Hi Harlan, long time no speak ;-)

I had posted a different solution because I'd already done something very
similar to what I believe the OP was after, but that having been said I'll tuck
that one away with the others, cheers.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Harlan Grove" wrote in message
...
"Ken Wright" wrote...
You are correct in that WEEKNUM is a PITA function that does not work
in an array. :-(
...

So improvise. If x were an array and you wanted what should be the array
result from WEEKNUM(x), try

=1+INT((x-DATE(YEAR(x),1,1)+WEEKDAY(DATE(YEAR(x),1,1),2))/7)




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004

If they are days then how are you going to represent a full year anyway?

The same formula will work for days but you simply run out of room.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"PR" wrote in message
...
Ken,
There are weeks days across the top, it could be weeks, but how can I say a
person has been employed for that week?

Paul


"Ken Wright" wrote in message
...
PR - What is the significance of the 1,2,3,4,5.......31 across the top? Are
those days, and if so how far out do they go, because you obviously cannot
chart a full year that way as you only have 256 columns.

Assuming they are weeks then I would suggest perhaps putting real dates in
there as opposed to those week numbers, and further assuming that you do
this, I'll assume that those dates are in say E1:BF1, your names/IDs are in
say B2:B200, your start dates are in C2:C200 and finish dates in D2200.

Now in cell E2 put the following formula and copy down and across to the
whole range E2:BF200

=IF(AND(E$1=$C2,E$1=$D2),1,"")

Done.

Obviously for those dates you can just put the first day of your year in E1
and then in F1 put =E1+7 and copy across.

If you prefer chart type data then format all the cells with a white font and
then use conditional formatting to colour the pattern of any cell that
contains a 1. You will still be able to do the maths at the bottom of the
sheet to get the total numbers on a weekly basis.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004

Ken,
when you say "If x were an array" do you mean the date?

Paul

"Ken Wright" wrote in message
...
LOL - Hi Harlan, long time no speak ;-)

I had posted a different solution because I'd already done something very
similar to what I believe the OP was after, but that having been said I'll
tuck that one away with the others, cheers.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Harlan Grove" wrote in message
...
"Ken Wright" wrote...
You are correct in that WEEKNUM is a PITA function that does not work
in an array. :-(
...

So improvise. If x were an array and you wanted what should be the array
result from WEEKNUM(x), try

=1+INT((x-DATE(YEAR(x),1,1)+WEEKDAY(DATE(YEAR(x),1,1),2))/7)




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004

when you say "If x were an array" do you mean the date?

That was Harlan's post you are referring to, but what he meant was that if x was
a range of dates that represented your array, ag B1:Z1 and as he stated, you
wanted what should be the array result from WEEKNUM(x), then you would
substitute your range for x in his formula

=1+INT((x-DATE(YEAR(x),1,1)+WEEKDAY(DATE(YEAR(x),1,1),2))/7)

giving you

=1+INT((B1:Z1-DATE(YEAR(B1:Z1),1,1)+WEEKDAY(DATE(YEAR(B1:Z1),1,1 ),2))/7)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"PR" wrote in message
...
Ken,
when you say "If x were an array" do you mean the date?

Paul




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004

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

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

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