Stacked Bar Time Charts?

**misscrf** · 07-02-2008, 05:46 PM

Is there a way to graph a chart by date (x axis) that shows stacked bars for overlapping time?

Ideally, I would have the columns like below:

Date ShoppingStart TimeShopping StoreAsStart TimeInStoreA StoreBStart TimeInStoreB

1/1/08 7:00AM 5 hrs 8:32AM 45 min 10:00AM 1.5 hrs

Then you would see a bar for that date, with 1 big bar for the day, with 2 colors inside that bar representing the time in shop a and the time in shop b. The y axis would be hours from say 6:00 AM - 7:00 PM. Does this make sense? The Time in A and Time in B could overlap. Any thoughts would be great!

**Andy Pope** · 07-03-2008, 05:25 AM

Can you post an example workbook. Use autoshapes to draw what you want.

How can the times overlap, surely you can not be in 2 places at once?

**misscrf** · 07-03-2008, 09:14 AM

Sure thing. I am attaching a pdf. I totally used autoshapes. I am trying to understand how I need to set the spreadsheet up so that the chart wizard will understand what I want, when I pick a stacked bar chart. Any help is greatly appreciated.

**Andy Pope** · 07-03-2008, 09:45 AM

Here is how the data would need to be laid out.

Still not sure how you can be in store B and A at the same time

Total + Pad total are on the primary axis
All other series are on the secondary axis

**misscrf** · 07-03-2008, 01:49 PM

how do you get a secondary axis like that? The time of A and B overlap, because I am using an analogy that is breaking down, lol.

I got everything plotted, but you can't even see it. I have time, duration, time duration, time duration for each series that I want to stack along date. When I do that, it tries to plot the time and the duration, when it should be duration beginning at a given time.

So, given I have the spreadsheet set up like yours, how do I get the chart wizard to plot it right?

Thanks again!

**Andy Pope** · 07-03-2008, 03:36 PM

Select the range A11:G16
plot data by Columns as a stacked column chart.

Double click the 3rd series, Pad A, and on the Axis tab set it to Secondary
Repeat for 4th, 5th and 6th series.
Select any of the recently moved series and change chart type to stacked column.

select the secondary Y axis and delete it. This will force all series to use the Primary axis.

format the 2 padding series to have no fill or border.
format the series A items to have the same fill.

**Audrey Lim** · 10-06-2008, 09:26 PM

Hello Andy,

I have something similiar to the above problems, instead I need to do a comparison for the stacked chart side by side, ie mth1 yr1, mth1 year2 for consecutive 12 months, within the stacked bar is the components of various sales products that add up to the total sales represented in Axis Y. Axis X would be the time frame. Any idea?

Thanks.

**Andy Pope** · 10-07-2008, 04:17 AM

Please don't hijack other peoples threads.

Post a new question, include a reference to this post if it is helpful.
Also include example file with your new post.

**misscrf** · 02-24-2009, 11:01 AM

You're funny, Andy. I've been hijacked!!! Anyway, I have come back to this again. I tried to lay out my data differently. If anyone has any further suggestions, I would love to try some new stuff. Here is a new stab at my dilema. I am trying to chart 3 time series by date and wondering if anyone might have some suggestions, please. This is what the data would look like:

Date Start End Duration Task
1/1/2009 8:00 AM 5:00 PM 9.00 Shopping
1/1/2009 12:00 PM 1:00 PM 1.00 Break
1/1/2009 8:15 AM 5:05 PM 8.50 Shopping Audit
1/2/2009 11:30 AM 1:05 PM 1.35 Break
1/2/2009 8:02 AM 5:12 PM 9.10 Shopping
1/2/2009 8:04 AM 4:12 PM 8.08 Shopping Audit
1/3/2009 8:00 AM 5:00 PM 9.00 Shopping
1/3/2009 11:45 AM 12:48 PM 1.03 Break
1/3/2009 8:17 AM 4:59 PM 8.42 Shopping Audit
1/4/2009 12:00 PM 1:00 PM 1.00 Break
1/4/2009 8:15 AM 5:05 PM 8.50 Shopping Audit
1/4/2009 8:00 AM 5:00 PM 9.00 Shopping
1/5/2009 11:30 AM 1:05 PM 1.35 Break
1/5/2009 8:02 AM 5:12 PM 9.10 Shopping Audit
1/5/2009 8:00 AM 5:00 PM 9.00 Shopping

What I want to show is a chart with times on the Y axis like from 7:30 AM to 5:30 PM. Then have the dates along the X axis. Each date would have a floating bar for the shopping in one color, a floating bar for the break in another color and a floating bar for the shopping audit in a third color. The point would be to show a pattern of the time of day that these events happen in relation to each other day by day. Any suggestions would be great. There may be a better way entirely to chart this data out. I am all ears.

Thanks!

**Andy Pope** · 02-24-2009, 11:43 AM

You are going to need to change the data layout again.

Chart all series as cluster column chart.
Move the Start Series to the secondary axis and format as no fill or border.
Remove the secondary axis.