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