title: Setting dates on X axis
body: <p>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:</p>
  
  <pre><code>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 &lt; minDate Then
              minDate = prop.SellDate
          End If
  
          If prop.SellDate &gt; 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
  </code></pre>
  
  <p>The properties variable in the For..Each loop is populated, and fills the arrays correctly.</p>
  
  <p>However, although the Scatter Graph data points are shown, the dates on the axis all show 1900. </p>
  
  <p>I tried adding the lines:</p>
  
  <pre><code>    .Axes(xlCategory, xlPrimary).MinimumScale = CDbl(minDate)
      .Axes(xlCategory, xlPrimary).MaximumScale = CDbl(maxDate)
  </code></pre>
  
  <p>Which showed the correct years along the axis, but now all the data points for both series have disappeared.</p>
  
  <p>I've tried a few other things, but it's been purely on a trial and error basis.</p>
  
  <p>The data is as follows</p>
  
  <p><img src="https://i.stack.imgur.com/IcjK6.jpg" alt="The data is as follows:"></p>
  
  <p>The resulting charts are:</p>
  
  <p>Correct dates, no data points</p>
  
  <p><img src="https://i.stack.imgur.com/JTG7G.jpg" alt="Correct dates, no data points"></p>
  
  <p>Incorrect dates, but we have data points</p>
  
  <p><img src="https://i.stack.imgur.com/Z0zcY.jpg" alt="Incorrect dates, but we have data points"></p>