What you need then is a gantt chart sort of approach. It's complicated
by the fact that you have to accommodate multiple conditions, often
repeated and often in any order, during the timespan of the chart. Your
data would look like this for two items. In the following, the first
item starts on 9/9, is quoted for 0 days, then is rented for 7 days,
then is quoted for the rest of the period. The second item starts on
9/9, is quoted for 7 days, rented for another 7, then quoted to the end
of the period.

Start Quoted Rented Quoted Rented etc.
28882 09/09/2005 0 7 X1
28883 09/09/2005 7 7 X2
etc.

Start is the first date on which an item has any status (could be the
start of the chart), and is a date. The rest of the items are durations.
X1 and X2 are for the duration to the end of the chart. This is a
stacked horizontal bar. Make the bar for the Start series invisible (no
border, no fill), format all Quoted series the same, and all Rented
series the same. Each like-named series is a separate series; there's no
way to have the same series recur in this manner.

For more on this approach, see:

http://pubs.logicalexpressions.com/P...cle.asp?ID=343
http://peltiertech.com/Excel/Charts/GanttChart.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

excelprogrammer wrote:

Thanks for your post,I followed your instructions about plotting as a
stacked column chart, I got a chart with two bars on the x axis, one
for Rented and one for Quoted, what i require is one bar showing both
Rented and Quoted status for the date range in the given data.

The original data was as follows

A1:28882 C1:Status
A2:09/09/2005 C2:Rented
A3:09/16/2005 C3:Quoted

This means that 28882,which is an equipment unit , has status Rented
from date 9 Sep to 15 Sep and has status Quoted from 16 Sep onwards
till end of month. This has to be shown in the chart with dates on x
axis and availability of equipment(Rented,Quoted,Available) on y axis
in different colours.

I understand that excel needs numerical values to plot, hence i am
trying to write a vba macro which will somehow manipulate and show
desired colours which i am trying using .ColorIndex.

Thanks and Regards