Dates in Excel 2007 graph axis dialogs

[JRS]

I work for an environmental engineering firm, and commonly create time-series
and other scatterplots of numerical data using Excel.

In Excel 2003, I could format a time-series scatterplot axis minimum and
maximum easily by typing in the dates in short date format. Not so in 2007 -
it refuses to accept dates in anything but "days elapsed since 01/01/1900"
format.

Is there a workaround for this? Why was it changed? It was quite useful.

Overall, I'm pretty unhappy with the new Excel. It seems to have acquired a
lot more layers of very questionable usefulness. Many of the new features in
the graphing (charting) function would be a lot more at home in Powerpoint
than in a bread-and-butter engineering program like Excel. Most of the
changes seem to be of the "because we can" variety rather than having been
driven by any real need or user feedback.

Thanks,

JRS

 

[Bernard Liengme]

A quick experiment confirmed your problem. Would a Line chart work for you?
The problem goes away and Line charts are appropriate for time series
charts.
best wishes

 

[JRS]

Line charts are only valid for categorical (non-numerical) data on the
independent axis, and I avoid using them. Previous versions of Excel have
made it very easy to unwittingly create useless "scatterplots" using the line
chart function. I haven't tried them on this version (2007). I've been told
recently they handle time-series data okay, though I've never looked into it,
having previously been satisfied with the scatterplot function. Thanks, I'll
have a look.

Still, I would like to see Microsoft bring Excel back up to its previous
level of usefulness. Why would they disable something like that? It makes
no sense...

 

[Bernard Liengme]

May I refer you to
http://peltiertech.com/Excel/ChartsHowTo/CatVsValueAxis.html
quote:
Line Chart: X as Time-Scale Axis
There is one exception to a Line Chart having evenly spaced categories. This
is the case of dates as X axis values. Excel calls this a Time-Scale axis,
but it is more accurately called a Date-Scale axis.

Time series ARE appropriately charted on LINE charts
best wishes

 

[Jon Peltier]

Only if the data are in integral dates. All times for a given date are
plotted at midnight at the start of the day (i.e., the integer part of the
date-time).

Removal of the ability to enter dates and times into the scale parameter
boxes for a value axis has been a sore loss.

- Jon

 

[JRS]

Okay, thanks Jon and Bernard. I commonly have multiple measurements per
date, so if the line chart only accepts integer dates, it won't work for me.
I'll be setting up little date conversion tables in all my worksheets that
have graphs again - haven't thought about that in years. Seems like we're
back to - when, 1995 or so?

I'm going to do some bellyaching about this one to Microsoft - maybe they
can get it fixed within the next couple of updates, or maybe I'm just being
optimistic.

Cheers,

- JRS

 

[Jon Peltier]

The whole dialog drives me crazy. Used to be one could click on the box with
the value, and it would automatically unselect auto and let you edit the
value. If you unchecked Auto, it would automatically select the value in the
box so you were right in edit mode. Now you have to check the Fixed box,
then click in the box and select the value, then start editing. One click to
three, and they claim the new UI is more productive.

Bellyache about this while you're at it.

- Jon

 

[Robert Davis]

I agree fully with this discussion and want to slap some heads over at
Microsoft. Useful features are supposed to be added, not taken away! In my
first experience with Excel 2007 I ran into this very issue. I deal with
large amounts of time based data from lab tests, and the time spacing is not
always consistent and line charts won't work. I now have to enter a
time/date into a spreadsheet cell, change the formatting to General, copy the
value of the cell from the entry bar, and then paste it into the
minimum/maximum field. Really annoying. Also, what happened to the "Sized
with Window" option for charts under the view menu?