Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
external usenet poster
 
Posts: 57
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding a single label on excel chart Eric_G Charts and Charting in Excel 1 October 2nd 09 10:22 PM
Dynamic chart - series label problem pignick Charts and Charting in Excel 2 May 27th 08 07:54 PM
Y- AXIS LABEL IS TRUNCATED in EXCEL CHART ? rama Charts and Charting in Excel 0 October 25th 06 08:28 AM
Category axis label problem at column chart Marko Pinteric Charts and Charting in Excel 9 April 24th 06 02:40 PM
Adding a certain text label in a excel chart Tim Charts and Charting in Excel 2 September 28th 05 08:03 PM


All times are GMT +1. The time now is 12:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"