Distorted date axis in Excel Charts

[jborcher]

I have had a consistent problem plotting time series data in Excel
charts. Specifically, dates shown on the X-axis of a plot will begin at
the year 1900 rather than at the correct date that is listed in the
Excel worksheet. Normally I work around this problem by reformatting
the X-axis of the chart, specifying the max and min date. However, I am
trying to automate production of hydrographs showing water level
variation with time in a large number of wells. I cannot reformat the
X-axis on all the plots individually. The odd thing is that the problem
is intermittent, that is, the macro that produces the hydrographs works
fine for some wells but for others the chart is distorted in time
(X-axis problem described above).

I'd like to mail the Excel workbook if someone could find the time to
look at it. I did not write the macro but I have modified it to 'work'
with the data in the worksheet.
The problem does not appear to be related to missing data at the
beginning or end of individual hydrographs.

All my dates are correctly formatted as dates (mm/dd/yyyy). After
filtering, sorting to find out of range dates, deleting the only two
incorrect dates I found (mm/dd/1900, and mm/212/yyyy) and deleting rows
with blank data, I still get charts with the time axis starting at the
year 1900. In fact 27 of 48 charts have this problem. I ran the same
checks and visually inspected the small work sheets created by the macro
I'm using. The data look fine, but even when building a chart manually
with these smaller sheets the time axis starts at the year 1900. Charts
for which there are only recent data (1990-2004) seem OK; charts with
data from the 1970s or earlier have the time-axis problem. Excel Help
files say that Excel can get confused by dates entered as mm/dd/yy but
all my dates are entered with four digit years.

The range of the Y-axis is also inappropriate in some cases. Charts seem
always to start at zero even if the water levels are within a small
range of values far removed from zero.

I was at my wits end some time ago.
Thanks for looking into this. I needed to be done with this two weeks
ago so any help you can provide will be greatly appreciated.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Trevor]

Fixing the axis on 27 charts by hand doesn't sound like a big deal, so I'm
assuming you're really concerned about the fact that you will either be
creating new charts or using your macro to recreate existing charts before
you're done with your project. so you want to update your macro? it
shouldl be fairly straight-forward, since the function which creates the
chart does have parameters for how to display the axes - and you can tell it
whether to automatically generate the scale or use a specific scale.

If you e-mail your spreadsheet to me at tsides at
intelligentsystemsconsulting (with the usual .com) then I can probably take
a look at it, but I'll warn you that while my charting and macro skills are
better than most users, there are tons of answerers in this news group who
are better than I. I suggest you copy/paste the code of your macro into a
reply to this post to see if anyone gives you an answer right away. But
since you're on a tight deadline, I can be looking at your file while you're
waiting for a response.

Trevor