If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
It works,
Many Thanks Paul R "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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 08:39 AM |
Microsoft Office 2003, Addons, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] | athens.gr. | General Discussions | 1 | September 3rd, 2004 02:43 AM |
Rule when primary key is needed? | Roland Bengtsson | New Users | 7 | August 16th, 2004 09:15 PM |
Loan Originators Needed | General Discussion | 0 | July 31st, 2004 07:10 PM | |
Expanding Data As Needed | MT | General Discussion | 2 | July 1st, 2004 12:52 AM |