stacked column with series in variable locations

[Guest]

I don't think this can be done within Excel, but then again, some of you have
some very creative ways of getting things done!

I have a client who would like to create a stacked column chart. Here is
some sample data.

Day 1 Day 2 Day 3
ab 6:00:00 AM 6:00:00 AM
cd 6:30:00 AM 2:50:00 PM
ef 9:15:00 AM 6:00:00 AM
gh 1:30:00 PM
hi 4:00:00 PM 6:45:00 AM 1:00:00 PM

The chart would have the Days along the X axis and the times along the Y.
Each day goes from 6:00 am - 6:00 pm.
On Day 1, Product ab is run from 6:00-6:30, Then cd from 6:30-9:15, etc.
Not all products are run on all days. Some days may have 10 products and
others only have 2.
He would like the chart to show the duration of each run but also indicate
the time span. The problem is, the products don't necessarily run in the same
order. Notice Day 2 data. A stacked column chart has no problem displaying
the duration of each run, but can't show the actual time span. He likes the
look of the stacked column - is there a different type of chart that could be
used?

Thank you!!

 

[Jon Peltier]

If the parts run out-of sequence, your table breaks down, because all it
indicates is when a product begins its run, not when it ends. If I
understand correctly, your data should be set up like this (a partial view
of the table):

Day 1 Day 2 Day 3
06:00 ab ab ef
06:15 ab ab ef
06:30 cd ab ef
06:45 cd hi ef
07:00 cd hi ef
07:15 cd hi ef
07:30 cd hi ef
07:45 cd hi ef
08:00 cd hi ef
08:15 cd hi ef
08:30 cd hi ef
08:45 cd hi ef
09:00 cd hi ef
09:15 ef hi ef
09:30 ef hi ef
09:45 ef hi ef
10:00 ef hi ef
10:15 ef hi ef
10:30 ef hi ef
10:45 ef hi ef
11:00 ef hi ef
11:15 ef hi ef
11:30 ef hi ef
11:45 ef hi ef

This can then be formatted to make the worksheet serve as your graphical
display.

To get an idea whether this suits your needs, manually color in the cells
uniquely for each product (e.g., ab = red, cd = blue, etc.). If it works
visually, then you could use VBA to color in each cell based on the product
in the cell. (There are going to be too many products, I'm sure, to use
conditional formatting, which up to and including Excel 2003 has a limit of
three conditions that can be applied, i.e., three products.)

If it works, post back and someone can help with the code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______