Timescale & X Axis Dates

[Vexorg]

This is driving me nuts ive looked up a couple of guides as well, nothing has worked. I have a data set and dates are displayed on the X axis. Excel automatically uniformly spaces these columns, its very misleading how can I solve?

Formatting the Axis to Date Axis doesn't do anything


Magnitude Date
5.5 8/4/1844
5.8 9/5/1944
5.3 10/7/1983
5.1 4/20/2002
3.6 5/24/2002
3.3 12/25/2002
5.8 8/23/2011

 

[Vexorg]

halp, i know there is a simple solutionn

 

[Vexorg]

bump

 

[Steve=True]

You may need to provide more information:

What is your Excel Version?

What type of graph are you making? Column or Line or ??

The other problem you have with your data is that it will automatically be converted to text because of your first value is outside of the valid Dates in Excel. Therefore your data will be plotted uniformly like a standard text based axis chart.

If you delete your first data point (8/4/1844) you will get the desired chart you are looking for, however, the columns are very very small and hard to see as they are a single pixel. That is because there are 24458 days between 9/5/44 and 8/23/11.

From Microsoft Excel Help

Excel interprets the year argument according to the date system your computer is using. By default, Microsoft Excel for Windows uses the 1900 date system; Microsoft Excel for the Macintosh uses the 1904 date system.

How does Excel for Windows work with the 1900 date system?

• If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, <CODE>DATE(108,1,2)</CODE> returns January 2, 2008 (1900+108).
• If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, <CODE>DATE(2008,1,2)</CODE> returns January 2, 2008.
• If year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value.

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

To make the chart you REALLY REALLY want with 1844 as a valid year, you will have to fudge the data by adding row for every day between 8/4/44 and 8/23/11. Howver, that is over 62000 days.

Alternately, you can add rows for every year between 1844 and 2011. This will be close, but since you had 3 instances in 2002, it will look a little strange, but may not be decernable by the human eye

Steve=True
www.exceldashboardtemplates.com