Chart Date Label Problem - Is this a bug in Excel?
 

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).Da taLabel.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

Chart Date Label Problem - Is this a bug in Excel?
 

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).Da taLabel.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