Plotting things along a single column

  • Thread starter Thread starter Suzie
  • Start date Start date
S

Suzie

DateNo Date NumdaysFromStartDate
Day1 1/1/2003 0
Day2 5/15/2003 134
Day3 2/1/2004 396
Day8 6/1/2004 517

I need to plot dates on a line of varrying length for projects we work on.
What I'd like to be able to do is have the user enter dates as in columnB
and then show those dates on a single column (or bar) thus making a time
line. Does anybody know how to do this?
 
I'm looking for something different. Imagine a timeline. What I want is
really a single/bar column chart that plots these dates on the same
column/bar.
 
Suzie -

Adjusted your data to give the number of days since the previous day in
the list:

NumdaysFromLatestDate
Day1 1/1/2003
Day2 134
Day3 262
Day8 121

The value for Day 1 is the start date, and the rest are elapsed days.
The cell above "Day1" is blank. Select this entire range, including the
top row with the blank cell, and make a stacked bar chart, with the data
in rows (Excel wants to do it by column). The chart has one bar from the
beginning up to 1/1/03, then the next from there to 5/15/03, etc.

I got reasonable time axis formatting with these settings:

Min: 37530.5
Max: auto scale
Major: 91.5

Number Format: mmm yyyy

- Jon
 
This is fantastic! Now that I've seen it I want to add more! Since this
gives me a bar that shows the duration, is there a way to plot the months
along that axis, too?
 
You can show the date just by double clicking the axis, and changing to
a Date format on the Number tab. Actually, that's at the end of my last
post.

Of course, it doesn't give a precise kind of date scale axis that you
get with a line chart, where you can have an axis tick at the first of
every month. The quickie one I provided gives you a tick at the first or
second of each month, and you have to hide the day in the date to make
it look okay. You can combine your chart with a line series to get a
better time scale axis, though.

Start with the stacked bar chart. Put some dummy data in the worksheet,
something like this:

Dummy
1/1/03 0
1/1/04 0

Copy this range, select the chart, and choose Paste Special from the
Edit menu, using the New Series, By Column, Names in First Row,
Categories in First Column option. It messes up the chart, but don't
give up. Right click the new series, which is yet another bar stacked on
top, choose Chart Type from the pop up menu, and select any line style.
If it's hard to select that series, select any series and press the
arrow keys until it is selected. Look for 'Series "Dummy"' in the name
box (above cell A1).

Now we have to rearrange the axes. The chart will change in appearance
during all of this, but remember, we're fixing it.

The axis on top of the chart has 2003 and 2004. Double click it, and on
the Scale tab, change the Base Unit to Day(s), and check the Value (Y)
Crosses at Maximum box.

Double click the left hand vertical axis, and check the Value (Y) Axis
Crosses at Maximum Category box on the scale tab.

Double click the right hand vertical axis, and uncheck the Value (Y)
Axis Crosses at Maximum Category box on the scale tab.

Choose the same reasonable min and max for both horizontal axes. For the
data I used in my earlier example, I used a minimum of 9/1/02 and a
maximum of 9/1/04. While you're at it, change the major spacing of the
lower horizontal axis to 1 or 2 Month(s).

Now hide all the unnecessary stuff. Format the dummy Line series to have
no line and no marker. Format the top and right axes to have no major or
minor tickmarks and no tick labels (on the Patterns tab after double
clicking).

The drawback to this approach is that you have to change both time
scales when the data changes, but it would be possible to write a macro
to do this for you.

- Jon
 
Back
Top