Problems charting x axis values

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

Ok, lets say I have a dates (3/14/94) in columns a2 and a25 and (3/15/04) in
columns a27 and a50 and times (0:00) in columns b2 and (11:00) in b25 and
the same time in columns b27 and b50. The problem is when I type in the
category x axis labels =sheet2!$a2:$b50 it will only label the first date in
columns a2 and a27 and not the ones in a25 and a50.

----------------------------------------------------------- x axis
0:00 0:00
Does this
3/14/94 3/15/04

----------------------------------------------------------- x axis
0:00 11:00 0:00
Want this
3/14/94 3/14/04 3/15/04

Any help would be appreciate
 
Mike -

There are a few issues. If your scale really does go from 3/14/94 to
3/15/04, the difference between 11:00 3/14/04 and 0:00 3/15/04 is tiny
(on a linear scale), 3 hours divided by 10 years. If the full scale is
360 pixels (large for an embedded chart), the gap between the last two
points on a linear scale is around 1/100 pixels.

How do you want to interpret these dates and times? If you want a
continuous variable, you can add the date and time together. A date by
itself means the date at midnight starting the date using integers
starting on 1/1/1900, and a time by itself is a fraction representing
the portion of the day elapsed at that time (noon = 0.5, 6 pm = 0.75).
When they are added together you get a decimal number, which Excel can
format as a date. Your three date and time values resolve to this:

34407.00000, 38060.45833, 38061.00000

What kind of chart are you making? A Line chart gives you the option of
a nice date scale axis, but any times during the day are rounded down to
midnight (apparently the line chart only does integers along the
category axis). A category type axis on a line chart (which you'll get
using two rows for the axis tick labels) will produce evenly spaced
points, regardless of the mathematical spacing of the values. An XY
Scatter chart treats date-time as a continuous variable, but it treats
multiple row labels as non numerical categories, assigned to integer
values 1, 2, 3, etc. Column charts and area charts treat the category
value the same way as line charts.

Maybe if you explained what you were trying to plot, the answer would be
easier than I've made it all sound.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top