Changing the value of dates on charts

[William VanWyden]

Can anyone tell me how I might change the value of dates in charts( usually
seen on the left side of a chart) to reflect EXACTLY what I have in my
spreadsheet?

Excel only seems to give me its rather strange interpretation of date
values. They appear presently both before and after my exact date. While
they are not wrong, per se, they are much to vague to be of use.


Thanks very much in advance!


Kind Regards,

W. VanWyden

 

[J.E. McGimpsey]

Can anyone tell me how I might change the value of dates in charts( usually
seen on the left side of a chart) to reflect EXACTLY what I have in my
spreadsheet?

Excel only seems to give me its rather strange interpretation of date
values. They appear presently both before and after my exact date. While
they are not wrong, per se, they are much to vague to be of use.


Thanks very much in advance!

What type of chart? Could you give an example of "vague dates", what
your data shows and what you'd like to see?

 

[William VanWyden]

What type of chart? Could you give an example of "vague dates", what
your data shows and what you'd like to see?

Thanks for replying, J.E.

The chart is a dates and items chart done in a 3D bar style.

The dates appear on the left top to bottom. They have values assigned like
November 2, 2002, February 10,2003 May 21, 2003. My bar chart corresponds
with these insofar as the bar ends between the dates listed but there is no
way to tell what date precisely they correspond to. The dates shown are not
the exact dates on my typed columns I created earlier.

IE: March, 20, 2004 item will not display as March, 20, 2004. It will
display as somewhere between, say, January 13, 2004 and April 25, 2004.

How may I correct this?


Thanks.


W. VanWyden

 

[J.E. McGimpsey]

Thanks for replying, J.E.

The chart is a dates and items chart done in a 3D bar style.

The dates appear on the left top to bottom. They have values assigned like
November 2, 2002, February 10,2003 May 21, 2003. My bar chart corresponds
with these insofar as the bar ends between the dates listed but there is no
way to tell what date precisely they correspond to. The dates shown are not
the exact dates on my typed columns I created earlier.

IE: March, 20, 2004 item will not display as March, 20, 2004. It will
display as somewhere between, say, January 13, 2004 and April 25, 2004.

How may I correct this?

Unfortunately, you can't format axis ticks to display data labels.
Axis ticks are always a fixed distance apart. You can CTRL-click the
axis and change the major and minor tick scale on the Scale tab
(i.e, change major scale to 1 day and there will be a label for each
day in the range), but I suspect it still won't provide what you
want.

You can use data labels, but by default they're at the right end of
the bar on a bar chart. This macro will resize the plot area, turn
off axis labels, and move the data labels to the left of the axis.

You'll probably want to tweak with the cLabelWidth constant - a
setting of 60 worked well for me on a variety of charts, but YMMV.

The chart must be selected when you run this macro.

Option Explicit
Public Sub DataLabelsToLeftAxis()
Const cLabelWidth As Integer = 60
Dim nMaxPlotWidth As Integer
Dim nMyLeft As Integer
Dim oPt As Point
Application.ScreenUpdating = False
With ActiveChart
nMaxPlotWidth = .ChartArea.Width - cLabelWidth
With .Axes(xlCategory)
.Border.LineStyle = xlNone
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNone
End With
With .PlotArea
.Width = Application.Min(.Width, nMaxPlotWidth)
.Left = Application.Max(.Left, cLabelWidth)
End With
nMyLeft = .Axes(xlCategory).Left - cLabelWidth
With .SeriesCollection(1)
.ApplyDataLabels _
Type:=xlDataLabelsShowLabel, _
AutoText:=True, _
LegendKey:=False
For Each oPt In .Points
With oPt.DataLabel
.Left = nMyLeft
.HorizontalAlignment = xlHAlignRight
End With
Next oPt
End With
End With
Application.ScreenUpdating = True
End Sub

If you're not familiar with macros: Type OPT-F11 to enter the Visual
Basic Editor. Click on the project in the Project Explorer window
that has your workbook's name. Choose Insert/Module menu item, and
paste the macro into the window that opens. Type OPT-F11 to return
to XL. Select the chart, then run the macro using
Tools/Macro/Macros..., selecting DataLabelsToLeft in the listbox and
clicking Run.

For more on getting started with macros, see David McRitchie's site:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[William VanWyden]

Unfortunately, you can't format axis ticks to display data labels.
Axis ticks are always a fixed distance apart. You can CTRL-click the
axis and change the major and minor tick scale on the Scale tab
(i.e, change major scale to 1 day and there will be a label for each
day in the range), but I suspect it still won't provide what you
want.

You can use data labels, but by default they're at the right end of
the bar on a bar chart. This macro will resize the plot area, turn
off axis labels, and move the data labels to the left of the axis.

You'll probably want to tweak with the cLabelWidth constant - a
setting of 60 worked well for me on a variety of charts, but YMMV.

The chart must be selected when you run this macro.

Option Explicit
Public Sub DataLabelsToLeftAxis()
Const cLabelWidth As Integer = 60
Dim nMaxPlotWidth As Integer
Dim nMyLeft As Integer
Dim oPt As Point
Application.ScreenUpdating = False
With ActiveChart
nMaxPlotWidth = .ChartArea.Width - cLabelWidth
With .Axes(xlCategory)
.Border.LineStyle = xlNone
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNone
End With
With .PlotArea
.Width = Application.Min(.Width, nMaxPlotWidth)
.Left = Application.Max(.Left, cLabelWidth)
End With
nMyLeft = .Axes(xlCategory).Left - cLabelWidth
With .SeriesCollection(1)
.ApplyDataLabels _
Type:=xlDataLabelsShowLabel, _
AutoText:=True, _
LegendKey:=False
For Each oPt In .Points
With oPt.DataLabel
.Left = nMyLeft
.HorizontalAlignment = xlHAlignRight
End With
Next oPt
End With
End With
Application.ScreenUpdating = True
End Sub

If you're not familiar with macros: Type OPT-F11 to enter the Visual
Basic Editor. Click on the project in the Project Explorer window
that has your workbook's name. Choose Insert/Module menu item, and
paste the macro into the window that opens. Type OPT-F11 to return
to XL. Select the chart, then run the macro using
Tools/Macro/Macros..., selecting DataLabelsToLeft in the listbox and
clicking Run.

For more on getting started with macros, see David McRitchie's site:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Thanks J.E.!

Best,

W.V.