Chart xy time

riccardo79

New Member
Joined
Dec 23, 2008
Messages
3
Hi all,
here there is my problem.

Let's suppose that I have a serie of activities. All activities are done in an interval of time and there is not overlapping.
Rich (BB code):
ACTIVITY DATA_START DATA_END 1 10/dic/2008 10.20 10/dic/2008 10.30 2 10/dic/2008 10.31 10/dic/2008 10.40 3 10/dic/2008 8.20 11/dic/2008 18.30 4 11/dic/2008 18.41 11/dic/2008 23.40 5 11/dic/2008 23.41 11/dic/2008 23.59​
Now, I need to build a chart like this one:​

Rich (BB code):
       ^
 23:59 |                          5
 23:41 |                          5
 23:40 |                          4
       |                          4
 18:41 |                          4
 18:30 |                          3
       |                          3
       |                          3
       |                          3
       |                          3
       |                          3
       |                          3
       |                          3
 10:40 |           2              3
       |           2              3
       |           2              3
 10:31 |           2              3
 10:30 |           1              3
       |           1              3
 10:20 |           1              3
       |                          3
       |                          3
 08:20 |                          3
       |
       +--------------------------------->
             10/dic/2008       11/dic/2008

I tried with stock chart but the result was not really good (just 1 color for all activities). I need a XY time chart. I'm using Excel 2003

Could someone help me?

Riccardo
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Do any activities overlap midnight (that's the real catch situation)? If not, it's easy to make a stacked chart if you reformat your data with formulas.
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Sorry, on second thought it's not so easy.

How many days of data do you need to put it, and how often does it change? How big will the chart be?
 

riccardo79

New Member
Joined
Dec 23, 2008
Messages
3
Hi guys,
sorry but I was on holiday!

However, there are no activities that overlap midnight. The number of days could be i.e. 6-7 days. and the activities are less then 100.

Thanks for the help,
Riccardo
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Assuming you have a week of values:
Excel Workbook
ABC
1ActivityStartEnd
2112/10/2008 10:2012/10/2008 10:30
3212/10/2008 10:3112/10/2008 10:40
4312/10/2008 18:2012/10/2008 18:30
5412/11/2008 18:4112/11/2008 23:40
6512/11/2008 23:4112/11/2008 23:59
7612/12/2008 9:4512/12/2008 10:00
87
98
109
1110
1211
1312
1413
15
Sheet1


Here is the formula-based data table (since there are only 3 activities max in the data set I made, I only put 3 activities worth of formulas down. Feel free to copy down a set of two values for as many activities as needed).

There are bound to be MUCH better ways to do this.


The resulting chart is what you're looking for though. Chart G3:M8 on a Stacked Chart, set the X axis labels as G1:M1, and then set no border/no fill for every odd numbered series (you can click them in the legend box if you can't see them to select them in the chart). That will make all the 'filler' entries invisible to leave you only with the activities highlighted. Format as desired.
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
And Excel just crashed as I realized I needed to extend the columns to 4 to create HTML for you.

The formulas are fine. Just copy rows 7-8 in the results table down an extra row and you'll be fine.
 

riccardo79

New Member
Joined
Dec 23, 2008
Messages
3
Hi Sal,
i'm not able to replicate your solution. Could you post or send me the file (riccardo dot russo 79 at gmail dot com)

Currently I'm using the "XY Scatter" with Error bars for the elapsed time of the activity

Riccardo
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
That's because you need to create a stacked bar chart with the output of the data I sent. As I said, Excel crashed, ergo I don't have any sheet to send. You should be able to create a data table with the info I posted, and then you should create a stacked bar graph, as stated in my earlier post.
 

Forum statistics

Threads
1,082,549
Messages
5,366,237
Members
400,881
Latest member
aforte

Some videos you may like

This Week's Hot Topics

Top