Using Datetime values as Category

[Dave Shea]

I am having trouble using regular Excel datetime values as
the x-axis values on a simple chart.

When I use Time only values, I have no problem, one bar or
column per time value. However when I use datetime values
I get either one very fat column (when the datetime values
cover only 1 day) or none at all and the x-axis shows
values of 31Dec for each year from 1900 onwards.

If anyone knows how to do this properly, or even a
workaround I would be pleased to hear it. My charting need
is to provide an updated chart on server performance. The
chart should cover 30 minute spans over the last 48 hours,
but I would like the x-axis labels to indicate the date as
well as the time.

Thanks.

Dave.

 

[Jon Peltier]

Hi Dave -

You can't use a "Time-Scale" axis for this. Excel should have labeled
these "Date-Scale" axes, because they scale with dates, but not with
times. Any time between midnight at the beginning of a day and midnight
at the end is treated as having occurred at the first midnight.

You could use a category-scale X axis, which just treats the category
labels as valueless text labels. Right click on the chart, choose Chart
Options from the pop up menu, click on the Axes tab, and change the
Primary X Axis from Time-Scale or Automatic to Category. Or you can
switch to an XY Scatter chart, exchanging columns for points and lines.

For more information about axis types and chart types, see my web pages:

X Axis: Category or Value?
http://www.geocities.com/jonpeltier/Excel/Charts/CatVsValueAxis.html

Scatter Chart or Line Chart?
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=190

- Jon