Setting dates on X axis

Question

I'm trying to create a chart in Excel VBA and am having problems getting the X-Axis to display the dates correctly; the code is below:

Function CreateChart()
Dim objChart As Chart

ReDim detached_price(detachedProps.count - 1) As Double
ReDim detached_date(detachedProps.count - 1) As Date

ReDim semi_price(semiProps.count - 1) As Double
ReDim semi_date(semiProps.count - 1) As Date

Dim minDate As Date
Dim maxDate As Date
minDate = Date

Dim detachedCount As Integer
detachedCount = 0
Dim semiCount As Integer
semiCount = 0

Set objChart = Charts.Add
With objChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Price Paid"
    .ChartType = xlXYScatter
    .Location xlLocationAsNewSheet
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
    .Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
    .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "yyyy"
    .Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
    .Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True

    For Each prop In properties
        Select Case prop.PropertyType
            Case "Detached"
                detached_price(detachedCount) = prop.Amount
                detached_date(detachedCount) = prop.SellDate
                detachedCount = detachedCount + 1
            Case "Semi-detached"
                semi_price(semiCount) = prop.Amount
                semi_date(semiCount) = prop.SellDate
                semiCount = semiCount + 1
        End Select

        If prop.SellDate < minDate Then
            minDate = prop.SellDate
        End If

        If prop.SellDate > maxDate Then
            maxDate = prop.SellDate
        End If
    Next

    .SeriesCollection.NewSeries

    .SeriesCollection(DETACHED).Name = "Detached"
    .SeriesCollection(DETACHED).Values = detached_price
    .SeriesCollection(DETACHED).XValues = detached_date

    .SeriesCollection.NewSeries
    .SeriesCollection(SEMI).Name = "Semi-Detached"
    .SeriesCollection(SEMI).Values = semi_price
    .SeriesCollection(SEMI).XValues = semi_date
End With End Function

The properties variable in the For..Each loop is populated, and fills the arrays correctly.

However, although the Scatter Graph data points are shown, the dates on the axis all show 1900.

I tried adding the lines:

    .Axes(xlCategory, xlPrimary).MinimumScale = CDbl(minDate)
    .Axes(xlCategory, xlPrimary).MaximumScale = CDbl(maxDate)

Which showed the correct years along the axis, but now all the data points for both series have disappeared.

I've tried a few other things, but it's been purely on a trial and error basis.

The data is as follows

The data is as follows:

The resulting charts are:

Correct dates, no data points

Incorrect dates, but we have data points

You say the properties are filled out correctly but the dates show up wrong on the axis? So the property in fact has the correct date, but the date shows up differently when plotted? Can you include a printout of the array? — chiliNUT
@chilliNut, thats about the crux of it :) I've included some screenshots to clarify what I mean, and also the raw data which is in the properties variable. — Eric Yeoman
Ah. My guess is that because you are using a non-standard date format (dd-mm-yyyy) instead of the more common (mm-dd-yyyy) excel is choking and not correctly interpreting the data as a date when it is charting it — chiliNUT
@chiliNUT: mm-dd-yyyy is used by a little region called ROW (rest of the world) so I think it qualifies as a standard format. — Jean-François Corbett
Thanks for the help chiliNut and Jean-François. I'm in the UK, so dd-mm-yyyy is used over here, the mm-dd-yyyy can cause much confusion :) Cool Blues answer sorted out the problem, setting types to Double instead of Date was the problem. Cheers! — Eric Yeoman

Cool Blue Cool Blue · Accepted Answer · 2013-11-21T03:57:34

Even though I disagree with their definition of "common" date format :q, I think that @chiliNUT is on to something. There seems to be some problem with the coercion of the date format.

If you change all of your Date type variables to Double or Long, it should work.

For example change

ReDim detached_date(detachedProps.count - 1) As Date

to

ReDim detached_date(detachedProps.count - 1) As Double

or

ReDim detached_date(detachedProps.count - 1) As Long

This way the dates are not converted into String by the XValues method. They are stored as date serial numbers and the axis routine is able to coerce them successfully into the local date format.

Whats happening in your code is the Date types are coerced to String by the XValues method and the axis rendering routine seems to be unable to coerce them back into dates properly.

I don't think it is actually related to the international settings as I tried it using dates like this:

1/01/2013
1/01/2014
1/01/2015
1/01/2016
1/01/2017
1/01/2018
1/01/2019
1/01/2020
1/01/2021

which works in either system.

I think its just a bug in the axis rendering routine where its unable to properly coerce strings into dates.

I would be interested to hear from others more knowledgeable than me.

Also, I'm curious about this:

.SeriesCollection.NewSeries

.SeriesCollection(DETACHED).Name = "Detached"
.SeriesCollection(DETACHED).Values = detached_price
.SeriesCollection(DETACHED).XValues = detached_date

How does it know which series to reference? Is DETACHED a constant you have defined elsewhere?

I would think this would be better:

with objChart.SeriesCollection.NewSeries
    .Name = "Detached"
    .Values = detached_price
    .XValues = detached_date
end with

Setting dates on X axis

1 Answers