how do I format dates in a chart

  • Thread starter Thread starter Bryan Kelly
  • Start date Start date
B

Bryan Kelly

I am trying to look at some housing prices. when I chart the following
data, I get a normal looking chart.
July 1, 1991
51,000

August 1, 1993
12,600

September 1, 1998
75,000

September 1, 2001
84,100

March 1, 2004
124,000



Then I add a second house to the mix and wind up with:
July 1, 1991
51,000

August 1, 1993
12,600

September 1, 1998
75,000

September 1, 2001
84,100

March 1, 2004
124,000

April 1, 1984
46,900

December 1, 1987
53,300

October 1, 1999
68,000



I am useing an X/Y scatter chart without connecting lines. I just want to
pairs to be plotted. The X scale is from Jan 0, 1990 to jul 6, 2009. When
I attempt to set the X scale, the min is 0 and the max is 40,000. What kind
of garbage is this? The date column is formatted as a date. How do I fix
this?
 
Bryan,

Excel manages dates as numbers, which is why you see values 0 an
40000.

Enter the min and max values of your scale in two cells and change th
format of these cells to 'number' from date. You will see five digi
numbers.

Use these values on your min and max values and format the axis t
date.

- Leni
 
Hello Lenin,
Thank for the response. I saw the numbers you mention, but I find that
rather absurd.(Excel, not you <grin>) For them to display the dates as
number in the formatting section is just totally idiot. Well, I have
diddled with the numbers until I have just about what I need so it will have
to do.

Until later,
Bryan
 
Why bother converting the dates to numbers? Type the dates in any format
Excel recognizes into the axis scale boxes. Excel converts the dates to
numbers when you exit the dialog. This also works for times.

- Jon
 
Interesting. I expected that theExcel designers would show the values in
the scaling window in the same format as the spread sheet. But so be it.

My data runs from Jan 71 through Nov 03. I want to visually extrapolate the
data to Mar 04. When I enter those values the chart go out to Nov 03 and
stops. Is there a remedy for this without adding in bogus data?

Thank you,
Bryan


Jon Peltier said:
Why bother converting the dates to numbers? Type the dates in any format
Excel recognizes into the axis scale boxes. Excel converts the dates to
numbers when you exit the dialog. This also works for times.

- Jon
Click to expand...
 
Bryan -

If it's an XY Scatter chart, you can just set the axis max to whatever
you want. If it's a line chart, you can get a time scale axis which also
allows you to set the axis endpoints. If Excel recognizes the data as
dates, it will likely provide the time scale axis by default; if not,
right click the chart, choose Chart Options from the pop up menu, and on
the Axes tab, select Time Scale under Category (X) Axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Bryan said:
Interesting. I expected that theExcel designers would show the values in
the scaling window in the same format as the spread sheet. But so be it.

My data runs from Jan 71 through Nov 03. I want to visually extrapolate the
data to Mar 04. When I enter those values the chart go out to Nov 03 and
stops. Is there a remedy for this without adding in bogus data?

Thank you,
Bryan
Click to expand...
 
Back
Top