| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Forulma Needed
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
"PR" schrieb im Newsbeitrag ... 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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
Jean,
sorry, it has not help. Paul "Jean Ruch" wrote in message ... "PR" schrieb im Newsbeitrag ... 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 |
|
#6
|
|||
|
|||
|
"Jean Ruch" schrieb im Newsbeitrag ... 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 |
|
#7
|
|||
|
|||
|
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" wrote in message ... "Jean Ruch" schrieb im Newsbeitrag ... 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 |
|
#8
|
|||
|
|||
|
"PR" schrieb im Newsbeitrag ... 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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
You are correct in that WEEKNUM is a PITA function that does not work in an
array. :-( -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "LanceB" wrote in message ... 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 --- 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 |
|
#11
|
|||
|
|||
|
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 D2:D200. 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 |
|
#12
|
|||
|
|||
|
"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) |
|
#13
|
|||
|
|||
|
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 D2:D200. 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 |
|
#14
|
|||
|
|||
|
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 |
|
#15
|
|||
|
|||
|
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 D2:D200. 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 |
|
#16
|
|||
|
|||
|
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 |
|
#17
|
|||
|
|||
|
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 |
|
#18
|
|||
|
|||
|
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 |
|
#19
|
|||
|
|||
|
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 |
|
#20
|
|||
|
|||
|
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 |
|
#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 |
|
#22
|
|||
|
|||
|
Paul,
I have done that and it works great. I would like to add one more thing to this if possible, how can I filter on departments, so that the numbers change so that it reflects just the department I have chosen. Paul "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 |
|
#23
|
|||
|
|||
|
OK. Still using the original sheet, do the following
In cell D1 put the text Department. Define cells D2:D6 with the name DEPT Put the Department names in these cells. Now go further down the sheet, where the main table is. There is a cell with the word WEEKLY in it. On my sheet this is in cell A14. In Cell A13 put the name of the department that you wish to summarise. Eg Department A. Next go to the main body of the table, the first cell to the right of the first date. This is cell B15 in my sheet. Change the formula in this cell. Change it to the following: =SUMPRODUCT((START<=$A15+B$13)*(END$A15+B$13)*(DE PT=$A$13)) Copy right to cell H15 and copy down as far as necessary. Now, every time you wish to see a different department, change the value in A13 to the department that you want to see. Regards Paul Allen "PR" wrote in message ... Paul, I have done that and it works great. I would like to add one more thing to this if possible, how can I filter on departments, so that the numbers change so that it reflects just the department I have chosen. Paul "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mail Merging Help Needed. Quickly! | Excel Discussion (Misc queries) | |||
| Help needed with textbox formatting in Excel 2000 | Excel Discussion (Misc queries) | |||
| Formula needed | Excel Worksheet Functions | |||
| Complex forumula help needed | Excel Worksheet Functions | |||
| Help Needed With - If-Function | Excel Worksheet Functions | |||
Linear Mode
