Excel Stacked Bar Chart Help PLEASE!

[aots123]

I have a very complex (at least complex for me) stacked bar chart to create. I have a sample that someone else created that I am trying to model after and I am having issues recreating it and need help. Here is my scenerio:

I need to create a stacked bar chart for a truckers schedule over a two month period. The driver has to document the 4 phases of the 24 hour day into three categories: 1) Off duty, 2) Sleeper, 3) Driving, 4) On Duty</SPAN></SPAN>. The driver is in and out of the four phases throughout a 24 hour period, all very randomly. The vertical axis needs to be the hours in the day, the horizontal axis is the days of the week, and in each day the four phases are color coded. I am able to get the stacked bar chart created but the way I have set the chart (not sure it is correct) with the data, excel thinks that my first entry is the same phase (i.e. the driver is always sleeping first thing [the row with that data is all sleeping time]), but it isnt always - it varies. The trucker isnt always sleeping in the beginning of the day, sometimes they are driving at that time, so I need to be able to alter the color coding or tell excel that is for a different phase. So I need to either alter the chart to direct it to the proper phase or set my data up differently on that sheet. I am good with formulas, charts are over my head!

The problem is that I dont know charts enough in excel to do this. I am up against a deadline, I need this done in 4 days, and I have been fighting with the program long enough.

Please help!! Thanks!

 

[theBardd]

How is your data organised on the sheet? And can it be modified if necessary?

 

[aots123]

How is your data organised on the sheet? And can it be modified if necessary?

Yes it can be modified, I am just not sure how to go about modifying it to best make the chart. Suggestions welcome. Right now I have it listed and color coded by date in a chart. See below:

Because this forum wont allow color I have put in the names of the phases next to them but in my chart each time is color coded. (each day should total 24 hours)

7/1/2013</SPAN>		7/2/2013</SPAN>
16</SPAN>	Sleeper</SPAN>	2.25</SPAN>	Driving</SPAN>
0.25</SPAN>	On Duty</SPAN>	0.25</SPAN>	On Duty</SPAN>
1</SPAN>	Driving</SPAN>	11</SPAN>	Sleeper</SPAN>
0.25</SPAN>	On Duty</SPAN>	0.5</SPAN>	On Duty</SPAN>
0.75</SPAN>	Off Duty</SPAN>	6.25</SPAN>	Driving</SPAN>
0.25</SPAN>	Driving</SPAN>	0.5</SPAN>	On Duty</SPAN>
0.25</SPAN>	Off Duty</SPAN>	3.25</SPAN>	Sleeper</SPAN>
2.25</SPAN>	Driving</SPAN>	0</SPAN>
0.25</SPAN>	Off Duty</SPAN>	0</SPAN>
2.75</SPAN>	Driving</SPAN>	0</SPAN>
0</SPAN>		0</SPAN>
0</SPAN>		0</SPAN>
0</SPAN>		0</SPAN>
0</SPAN>		0</SPAN>
0</SPAN>		0</SPAN>
0</SPAN>		0</SPAN>
0</SPAN>		0</SPAN>
0</SPAN>		0</SPAN>
0</SPAN>		0</SPAN>
Off Duty</SPAN>
Sleeper</SPAN>
Driving</SPAN>
On Duty</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=4></COLGROUP>

I have not figured out a way to make a chart to give me a 24 hour time span stacked with 4 different phases to give me the bar chart I am looking for. All the stacked bar chart examples I can find online are much simpler data.

 

[Cool blue]

Hi, you will need to summarise the data first using your formula skills.
You need a separate series for each phase: Sleeping, Driving and off duty. You need one number per day for each of those.

 

[theBardd]

I am still not sure I get it, but I would ditch the colour (you can't chart by colour), and you would need to add all of the time components over one day. What about other drivers? Your workbook might be the best help.

 

[aots123]

Hi, you will need to summarise the data first using your formula skills.
You need a separate series for each phase: Sleeping, Driving and off duty. You need one number per day for each of those.

Please explain in more detail what you mean, I dont quite understand.... is this what you mean?
Previously I had my data listed like this:

7/1/2013</SPAN>	7/2/2013</SPAN>
16</SPAN>	2.25</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
0.25</SPAN>	0.25</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
1</SPAN>	11</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
0.25</SPAN>	0.5</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
0.75</SPAN>	6.25</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
0.25</SPAN>	0.5</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
0.25</SPAN>	3.25</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
2.25</SPAN>	0</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
0.25</SPAN>	0</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
2.75</SPAN>	0</SPAN>
7/1/2013</SPAN>	7/2/2013</SPAN>
0</SPAN>	0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

But I had the same issue with the stacked bar chart and being able to designate between the different phases of on duty, off duty, sleeper, and driving status.

 

[aots123]

I know that I cannot chart by color, I am not trying to chart by color, I need the bar chart to be by color and the bars to reflect the phase relativative to a color. My workbook is set up by color just as a reference for data.

 

[Cool blue]

Suggest you read up on the basics of Charts in excel...
Charts are made up of series, each series is a row or a column of numbers.
You can have more than one series and in your case, they can be stacked.
You also need a list of categories, in your example dates, that are used to label the bottom axis (category or X axis).

Each series is a single row (or column) of numbers and theya must all be the same length as the categories which is also a single row (or column) of data.

So you need to organise your spread sheet with this in mind.

Make a table with 4 rows (or columns)...
1. Dates (this is the category or X axis labels)
2. Total Sleep time for each day (this is series 1)
3. Total work time for each day (series 2)
4. Total off duty time for each day (series 3)

 

[aots123]

I dont think you are realizing what I am trying to complete with the stacked bar chart. I dont need the total hours of driving, sleeping, etc per day. That chart is quite simple. I need the chart to be be a stacked bar to show the time in which the driver was in each phase, ie. on duty for two hours, sleeping for four, off duty for five, sleeping for eight, etc..... stacked in a bar vertically. This is why it needs to be in the format listed above in separate chunks of time and color coded. If I could upload a picture to this forum it would be quite clear because I have an example to follow, it is just recreating it is the problem. I have the data in place, it is the key and the coloring that I am having the most issues with.

 

[Cool blue]

Each day needs a stack of 3 bars: one for sleep, one for drive and one for off duty... no?

If not then its very complicated...