Hi

I have a chart created from three different worksheets. They are lists of
defects grouped on dates, and each sheet contains one status.

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):
Date New Defects
27.05.2008 9
28.05.2008 14
29.05.2008 14

Date Fixed Defects
28.05.2008 7
29.05.2008 6
30.05.2008 8

Date Closed Defects
29.05.2008 1
02.06.2008 9
03.06.2008 3

So the lines in the chart does not align properly. Two of the lines end far
right in the chart with no x-axis labels, for instance.

See results here: http://www.questus.no/Default.aspx?tabid=38
Especially notice the bottom right corner.

How can I align these lines? How can I make sure the line values fall on
correct dates along the x-axis?

Any help appreciated.

Regards,
Rolf

You forgot to tell us what type of chart you are using. If that is a line
chart, it may be worth seeing what happens when you change it to an XY
chart.
You have also made it difficult to see what's happening by choosing smoothed
lines, rather than straight lines, and by not displaying markers for the
data points.
--
David Biddulph

"Rolf Barbakken" <(E-Mail Removed)> wrote in message
news:13906C77-4E15-46EA-AACC-(E-Mail Removed)...
> Hi
>
> I have a chart created from three different worksheets. They are lists of
> defects grouped on dates, and each sheet contains one status.
>
> 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):
> Date New Defects
> 27.05.2008 9
> 28.05.2008 14
> 29.05.2008 14
>
> Date Fixed Defects
> 28.05.2008 7
> 29.05.2008 6
> 30.05.2008 8
>
> Date Closed Defects
> 29.05.2008 1
> 02.06.2008 9
> 03.06.2008 3
>
> So the lines in the chart does not align properly. Two of the lines end
> far
> right in the chart with no x-axis labels, for instance.
>
> See results here: http://www.questus.no/Default.aspx?tabid=38
> Especially notice the bottom right corner.
>
> How can I align these lines? How can I make sure the line values fall on
> correct dates along the x-axis?
>
> Any help appreciated.
>
> Regards,
> Rolf

On Thu, 26 Jun 2008, in microsoft.public.excel.charting,
Rolf Barbakken <(E-Mail Removed)> 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 (E-Mail Removed),
which goes to a spam folder-- please send your email to del3 instead.

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 <(E-Mail Removed)> 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 (E-Mail Removed),
> which goes to a spam folder-- please send your email to del3 instead.
>

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" <(E-Mail Removed)> wrote in message
news:EAE9B739-3396-4D66-AF0A-(E-Mail Removed)...
> 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 <(E-Mail Removed)> 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
>> (E-Mail Removed),
>> which goes to a spam folder-- please send your email to del3 instead.
>>

On Fri, 27 Jun 2008, in microsoft.public.excel.charting,
Rolf Barbakken <(E-Mail Removed)> said:
>"Del Cotter" wrote:
>> Rolf Barbakken <(E-Mail Removed)> said:
>> >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.

>I've added a XY version
>Although the defect lines look fairly good, the dates are way off. Not sure
>why this is.

It looks to me as if you haven't given it the dates at all. The
positions of the data points are date-formatted versions of 1, 2, 3, 4,
5 etc., which is what Excel defaults to if it hasn't got anything else
to work with.

Although an XY graph is supposed to be able to handle the word "Date"
over the date column without breaking, try taking the word out, leaving
a blank above the dates, and see if it properly picks up the X series.
Create your graph using only the first, ("fixed") series at first, then
add the other two manually using Cut and Paste Special, so that they
pick up their own X ranges instead of the first series X range.

Or use Jon Peltier's Quick XY Chart Utility to make the XY charts easily
with your chosen X and Y ranges:

http://www.peltiertech.com/Excel/Cha...rtUtility.html

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

I suspect the problem is that the "dates" are entered in a nonstandard
format, which Excel treats as valueless text.

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


"Del Cotter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Fri, 27 Jun 2008, in microsoft.public.excel.charting,
> Rolf Barbakken <(E-Mail Removed)> said:
>>"Del Cotter" wrote:
>>> Rolf Barbakken <(E-Mail Removed)> said:
>>> >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.
>
>>I've added a XY version
>>Although the defect lines look fairly good, the dates are way off. Not
>>sure
>>why this is.
>
> It looks to me as if you haven't given it the dates at all. The positions
> of the data points are date-formatted versions of 1, 2, 3, 4, 5 etc.,
> which is what Excel defaults to if it hasn't got anything else to work
> with.
>
> Although an XY graph is supposed to be able to handle the word "Date" over
> the date column without breaking, try taking the word out, leaving a blank
> above the dates, and see if it properly picks up the X series. Create your
> graph using only the first, ("fixed") series at first, then add the other
> two manually using Cut and Paste Special, so that they pick up their own X
> ranges instead of the first series X range.
>
> Or use Jon Peltier's Quick XY Chart Utility to make the XY charts easily
> with your chosen X and Y ranges:
>
> http://www.peltiertech.com/Excel/Cha...rtUtility.html
>
> --
> Del Cotter
> NB Personal replies to this post will send email to
> (E-Mail Removed),
> which goes to a spam folder-- please send your email to del3 instead.

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" <(E-Mail Removed)> wrote in message
> news:EAE9B739-3396-4D66-AF0A-(E-Mail Removed)...
> > 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 <(E-Mail Removed)> 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
> >> (E-Mail Removed),
> >> which goes to a spam folder-- please send your email to del3 instead.
> >>
>
>
>

> 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" <(E-Mail Removed)> wrote in message
news:0DE51192-1C25-4926-BACE-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in
>> message
>> news:EAE9B739-3396-4D66-AF0A-(E-Mail Removed)...
>> > 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 <(E-Mail Removed)> 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
>> >> (E-Mail Removed),
>> >> which goes to a spam folder-- please send your email to del3
>> >> instead.
>> >>
>>
>>
>>

I've had trouble submitting posts to MS newsgroups, but I solved the matter
on Monday.

What I did was to create new dates in a third column. I created them using
the Datevalue() function, and then formatted the dates as dd.mm.yyyy. I then
set this column as the X-values for the graphs.

This solved everything!

Thank you for all help.

"Rolf Barbakken" <(E-Mail Removed)> wrote in message
news:13906C77-4E15-46EA-AACC-(E-Mail Removed)...
> Hi
>
> I have a chart created from three different worksheets. They are lists of
> defects grouped on dates, and each sheet contains one status.
>
> 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):
> Date New Defects
> 27.05.2008 9
> 28.05.2008 14
> 29.05.2008 14
>
> Date Fixed Defects
> 28.05.2008 7
> 29.05.2008 6
> 30.05.2008 8
>
> Date Closed Defects
> 29.05.2008 1
> 02.06.2008 9
> 03.06.2008 3
>
> So the lines in the chart does not align properly. Two of the lines end
> far
> right in the chart with no x-axis labels, for instance.
>
> See results here: http://www.questus.no/Default.aspx?tabid=38
> Especially notice the bottom right corner.
>
> How can I align these lines? How can I make sure the line values fall on
> correct dates along the x-axis?
>
> Any help appreciated.
>
> Regards,
> Rolf