how to show date on the x axis of a line chart?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have three columns of data. The first column is date in the format of
dd-mmm-yy hh:mm:ss. The second and third columns have the real data I like to
chart for. I select three columns of data and draw a line chart, but date
doesn't show up in x axis. How do I fix this?

Thanks much for your help!
Ginger
 
select the two lines of data other than the date and make your chart with
them using <Chart><sourse data><series> enter the x axis data in the
approrite location.
 
Ginger,

Excel is reading your original X axis values as a time format. If you
double-click on your X axis label, you'll see in the Format Axis dialog box
-> Scale tab that the minimum time X axis value is one day. Given that
you're tracking hours, the one-day minimum value will not work for you.

To get around this limitation, add a helper column to your chart source.
Assuming your original data looks like this and is in the range A1:C6:

Time Entry Series1 Series2
05-Jan-05 01:00:00 5 5
05-Jan-05 02:00:00 4 3
05-Jan-05 03:00:00 6 4
05-Jan-05 04:00:00 7 6
05-Jan-05 05:00:00 8 5

add a helper column to column B. In the example below I've titled the
helper column "Text Entry". Enter the following formula to cell B2 and copy
it down column B:

=TEXT(A2,"yy-mmm-dd hh:mm:ss")

The revised data set look like this in the range A1:D6.

Time Entry Text Entry Series1 Series2
05-Jan-05 01:00:00 05-Jan-05 01:00:00 5 5
05-Jan-05 02:00:00 05-Jan-05 02:00:00 4 3
05-Jan-05 03:00:00 05-Jan-05 03:00:00 6 4
05-Jan-05 04:00:00 05-Jan-05 04:00:00 7 6
05-Jan-05 05:00:00 05-Jan-05 05:00:00 8 5

Column B will now be interpretted as a text entry that looks just like a
time entry.

Finally, create your chart using column B instead of column A as the X axis.
If you use the Chart Wizard, select the range B1:B6 to create the chart.
 
If your x-values are not equivally spaced, there is little in practical
terms that one can do with a line chart. However, a XY Scatter chart
just might be what the doctor ordered.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005
 
Except that using a Line chart gives you more control over the display of the axis
(try to put a tick mark on just the first of every month in an XY Scatter chart),
and the points are proportionally spaced by day, and the times of day are ignored.

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