I think you are looking for a Gantt chart. This can be done in Excel.
http://peltiertech.com/Excel/Charts/GanttLinks.html
I like to use the worksheet formatting technique. Described:
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343
Using the formatting technique:
Client:FromDate:ToDate:UserDate:NextDate:NextDate
If Client is in A1, FromDate is B1 and ToDate is C1
1) Set a user-entered date in D1
2) E1 becomes =D1+1 (or D1+7 for week-at-a-time)
3) Copy the E1 formula across as many columns as you need
4) Change the alignment of cell D1 across to 90% and narrow the columns so
the width of the newly-aligned date fits without too much white space
between.
5) Enter in D2 the following formula
=IF(AND(D$1>=$B2,D$1<=$C2),1,0)
6) Change Cell D2 to a custom format ;;;
7) Apply a conditional format to D2 so if the cell value is equal to 1 it
displays the pattern colour you wish, borders top and bottom.
8) Copy D2 and paste it down to the number of clients row required and
across to the range of date columns required.
{Later - after you copy D2 to the required range and have date entered} To
add ends to your bars, add a conditions to check whether the previous cell
to the left contains a zero. Use the same format but add a border to the
left as well as other borders. The process is similar for adding a border
to the right with changes that you should be able to modify of you added
the left border successfully}.
9) Optional - to break up the weeks, conditionally format your dates D1:?1
so it highlights your chosen weekday.
It may seem complex but the end result makes it worth the effort.
--
Lil
Hi - I need to track clients who are on a time limited program at
certain sites for a certain period of time.
eg. client 1 started 01-Jul-2009 at Siet A and is due to finish on
28-Jul-2009
client 2 started 05-Jul-2009 at Site B and is due to finish on
16-Jul-2009
The resultant graph needs to show start date through to finish date.
We need to be able to see how many people are on the program at any
given time and when we need to move people off to another program so
new clients can come on.
Hope that made sense
--
