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 :-)

Do you mean that you want a Gantt chart type of display? If so, Jon
Peltier shows how you can produce this he

http://pubs.logicalexpressions.com/P...cle.asp?ID=343

Hope this helps.

Pete

On Jul 15, 6:43*am, KerryInOz
wrote:
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 :-)

Excel 2007 PivotTable
With Gantt chart and
count of concurrent client dates per site.
Macro for chart title and step graph.
http://www.mediafire.com/file/ololyztmy0z/07_15_09.xlsm

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/P...cle.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



?B?S2VycnlJbk96?= wrote in
:

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 :-)




--

Wonderful!! Thank you everyone - I am going to play with this later today to
see if I can do it. The suggestions were exactly what I was looking for :-)

Thanks Again,

Krry.

"KerryInOz" wrote:

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 :-)