B
brencam
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
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