Forulma Needed

[PR]

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

 

[LanceB]

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)

 

[Jean Ruch]

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.

Hallo Paul,

I understood you differently from Lance.
To make your chart, instead of taking a series of NUMBERS 1,2,3, for
your x Axis, take real DATES
(01.01.2004, 02.01.2004, etc )
Make a stacked column chart of your data's including the row headers
p1, p2, p3 etc...
Does it come nearer to what you want ?

regards

Jean

 

[PR]

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

Paul

 

[PR]

Jean,
sorry, it has not help.

Paul

Hallo Paul,

I understood you differently from Lance.
To make your chart, instead of taking a series of NUMBERS 1,2,3, for
your x Axis, take real DATES
(01.01.2004, 02.01.2004, etc )
Make a stacked column chart of your data's including the row headers
p1, p2, p3 etc...
Does it come nearer to what you want ?

regards

Jean

 

[Jean Ruch]

Hallo Paul,

I understood you differently from Lance.
To make your chart, instead of taking a series of NUMBERS 1,2,3, for
your x Axis, take real DATES
(01.01.2004, 02.01.2004, etc )
Make a stacked column chart of your data's including the row headers
p1, p2, p3 etc...

Hello Paul,

To make the TABLE on which your chart is based:

Assuming your Entry "p1" is located in A5; "p2" in A6; "p3" in A7
etc...
and correspondingly the first day of work in Column B
the last day of work in Column C,
This whole basic Data beeing in the Range A5:C9

make a Date series in the Range F 10: AJ 10
(for ex. beginning with 01.01.2004 in F10 and ending with 31.01.2004 in
AJ 10 )

Put the following Formula in F11

=IF(F$10<$B5; ""; IF(F$10>$C5;"";1))

copy and insert it on all the concerned cells.

be cautious about the semicolon (german Excel); may be it is different
for you ?

regards

Jean

 

[PR]

Jean,
that works, I had to change the semicolon for a comma. this has brought the
days out, but how will I do this for a week period.

Paul

 

[Jean Ruch]

Jean,
that works, I had to change the semicolon for a comma. this has brought the
days out, but how will I do this for a week period.

Paul

Paul,

I don't understand what you mean exactly.

Could the following be of interest
The Function WEEKDAY(Date; 2) could help to discard all saturdays and
sundays in making the table, so that there would be no personal quoted
for these two days

(please see the online Help for this function for different options )

If instead of the dates, you put the formula = IF WEEKDAY( "the Date";
2) > 5; ""; "the Date")
(Also with the German notation especially about the semicolons )

For that, I put a supplementary date series in the row just above that
one we had up to now (F9;AJ 9) serving as ref. and the indicated
Formula in the Range F10 : AJ 10

The Graph changes correspondingly, Saturdays and Sundays don't have
Columns


I hope I was clear enough

regards
j
Jean

 

[LanceB]

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

 

[Ken Wright]

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

 

[Ken Wright]

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.

 

[Harlan Grove]

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)

 

[PR]

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]

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.

 

[Ken Wright]

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.

 

[PR]

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

Paul

 

[Ken Wright]

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)

 

[Paul Allen]

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)*(END>A$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]

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]

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