It's not as simple as entering the dates a "true date format". The data is
extracted from an Oracle database to excel, and I can expect several years
of
data.

It's not simple, but it's what you have to do. How does Oracle store the
"dates"?

If the dates are in a column as text in this format:

27.05.2008
28.05.2008
29.05.2008

select the column, and use Data menu Text to Columns to fix it. In step
three of the wizard, choose Date as the Column Data Format (top right of the
dialog), and choose DMYas the date order. The column will be converted to
dates, which will be displayed in whatever format you want.

Is it not possible to just tell Excel the date format and expect Excel to
treat the dates as dates?

If the contents of the cells are dates, then yes. If the contents are text,
you have to convert the text first. See above.

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


"Rolf Barbakken" wrote in message
...
I noticed the dates, yes.

It's not as simple as entering the dates a "true date format". The data is
extracted from an Oracle database to excel, and I can expect several years
of
data.

Anyway, I formatted the output from Oracle as dd-mon-yyyy (01-jan-2008,
for
instance - using function to_char(MIN(AUDIT_LOG.AU_TIME),'dd-mon-yyyy')),
and
Excel does not handle it well. I now have a chart with dates from
00.jan.00
to 15.mar.23
All charts based on these dates are now "strange" with lines going in
loops,
for instance.

Additionally, Excel now fails to create subtotals, and the sorting is way
off.

I've uploaded a new image at http://www.questus.no/Default.aspx?tabid=38
Notice the title? Seems to be using dateserials/datevalues.

To me, it seems Excel is guessing too much on the dates/date formats.
Everything I do must be possible to do in VBA, and fixing the dates have
been
the number one problem all along, but I seemed to fix it for the
lines-charts
for just one defect type, but combining the data from several types failed
as
previously stated. I removed all date fixing code for these tests, though,
so
there was no interfering by my code.

Is it not possible to just tell Excel the date format and expect Excel to
treat the dates as dates?

"Jon Peltier" wrote:

Notice the axis minimum is at 00.01.1900? That's the same as 0-Jan-1900,
or
zero the way Excel counts dates. If your dates are entered into the cells
in
dd.mm.yyyy format, Excel DOES NOT recognize them as dates and treats them
as
text labels. The chart can't plot text on a value axis, so it uses the
counting numbers 1, 2, 3, etc., for X values in the XY chart.

Enter your dates in true date format (like 1-Jan-2001) so Excel knows
they
are dates, then use a number format in the cells of dd.mm.yyyy, if that's
what you like.

I'm glad you've added markers and straightened the lines. Now you should
change the background from gray to white, and change the gridlines to
light
gray, or remove them altogether.

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


"Rolf Barbakken" wrote in
message
...
I've added a XY version to http://www.questus.no/Default.aspx?tabid=38
(bottom image)

Although the defect lines look fairly good, the dates are way off. Not
sure
why this is.

"Del Cotter" wrote:

On Thu, 26 Jun 2008, in microsoft.public.excel.charting,
Rolf Barbakken said:
The x-axis has dates, the y-axis has number of defects.

The problem seems to be aligning the different lines from these
worksheets.
The groups are not on the same dates, so I have three tables that can
look
like this (only first three, sorted):

You've used a Line Chart. Use an XY (Scatter) Chart instead.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3
instead.