I've created a chart with a 3 data series - one of which I am using as
a dummy X axis (using categories, rather than values). The chart is XY
scatter type.

I'm using VBA to apply the values to the labels on the X axis. The
labels are of the format mmm/yy. (I want the chart to show the
mth/year for the datapoints).

The underlying data (which I retrieve from an Access application)
takes care of converting the real date field in the database to a text
field of the form "'" & DateValue. (I actually prepend the single
quote to the date field in the SQL query so that Excel will treat the
label value as a text field). So far, so good!

Now for the problem - Although the data labels appear correctly
formatted in the Excel worksheet (i.e. they contain 'Oct96; 'Mar/97;
'Jun/01 etc.), they do not appear correctly on the chart. FYI the
series labels are assigned by the following VBA code

ActiveChart.SeriesCollection(3).Points(Counter).DataLabel.Text =
_
rng.Cells(Counter, 1).Offset(0, 6).Value

When I run the macro to apply labels, labels with values such as
'Jun/01, 'May/02 are dispayed incorrectly (1-Jun; 2-May etc.). I also
notice that 'Oct/96 is displayed as Oct-96 on the chart. What I want
is: Oct/96; May/01 etc.

From the preceeding it would appear that Excel is ignoring the
preceeding single quote in the data field (which tells Excel to treat
the cell as a text field) and treating the remainder as a date field.
This is not what I would expect to see. Is there a workaround for this
or am I missing something obvious? Has anyone else seen this
behaviour with charts?

BTW - I want to keep the mmm/yy (2 digit year) format on the chart if
possible to conserve chart space.

TIA,
Brendan

Brendan -

This might work. Enter the date as March 2001, and apply a custom
number format of "mmm/yy". This coerces the number, which Excel
recognizes as a date, into your desired format. When these values are
used in the labels, by default the number format of the label is taken
from that of the source cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

brencam wrote:
> I've created a chart with a 3 data series - one of which I am using as
> a dummy X axis (using categories, rather than values). The chart is XY
> scatter type.
>
> I'm using VBA to apply the values to the labels on the X axis. The
> labels are of the format mmm/yy. (I want the chart to show the
> mth/year for the datapoints).
>
> The underlying data (which I retrieve from an Access application)
> takes care of converting the real date field in the database to a text
> field of the form "'" & DateValue. (I actually prepend the single
> quote to the date field in the SQL query so that Excel will treat the
> label value as a text field). So far, so good!
>
> Now for the problem - Although the data labels appear correctly
> formatted in the Excel worksheet (i.e. they contain 'Oct96; 'Mar/97;
> 'Jun/01 etc.), they do not appear correctly on the chart. FYI the
> series labels are assigned by the following VBA code
>
> ActiveChart.SeriesCollection(3).Points(Counter).DataLabel.Text =
> _
> rng.Cells(Counter, 1).Offset(0, 6).Value
>
> When I run the macro to apply labels, labels with values such as
> 'Jun/01, 'May/02 are dispayed incorrectly (1-Jun; 2-May etc.). I also
> notice that 'Oct/96 is displayed as Oct-96 on the chart. What I want
> is: Oct/96; May/01 etc.
>
> From the preceeding it would appear that Excel is ignoring the
> preceeding single quote in the data field (which tells Excel to treat
> the cell as a text field) and treating the remainder as a date field.
> This is not what I would expect to see. Is there a workaround for this
> or am I missing something obvious? Has anyone else seen this
> behaviour with charts?
>
> BTW - I want to keep the mmm/yy (2 digit year) format on the chart if
> possible to conserve chart space.
>
> TIA,
> Brendan