title: VBA DateAdd not working as expected
body: <p>I am using the DateTime.DateAdd VBA function in Excel to create a column of dates. Using the code below, the day does not change as expected when the time progresses to midnight.</p>
  
  <pre><code>Sub test()
      Dim i As Integer
      Dim currentDate As Date
  
      With ThisWorkbook.Sheets(1)
          .Cells.Clear
          .Cells(1, 1) = "DateTime"
          .Cells(1, 2) = "Day"
  
          currentDate = CDate("10/3/2016 11:59:30 PM")
  
          For i = 1 To 10
              .Cells(i + 1, 1) = currentDate
              .Cells(i + 1, 2) = DateTime.Day(currentDate)
              currentDate = DateTime.DateAdd("s", 5, currentDate)
          Next i
  
          .Columns(1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
      End With
  End Sub
  </code></pre>
  
  <p>Output:</p>
  
  <ul>
  <li>DateTime | Day</li>
  <li>10/3/2016 11:59:30 PM | 3</li>
  <li>10/3/2016 11:59:35 PM | 3</li>
  <li>10/3/2016 11:59:40 PM | 3</li>
  <li>10/3/2016 11:59:45 PM | 3 </li>
  <li>10/3/2016 11:59:50 PM | 3</li>
  <li>10/3/2016 11:59:55 PM | 3</li>
  <li>10/<strong>3</strong>/2016 12:00:00 AM | <strong>4</strong>  <em>(day values do not match!)</em></li>
  <li>10/4/2016 12:00:05 AM | 4</li>
  <li>10/4/2016 12:00:10 AM | 4</li>
  <li>10/4/2016 12:00:15 AM | 4</li>
  </ul>
  
  <p>Changing the number format to military time gives a similar result. If I use the output dates as the x-values for a chart, the chart will use the 'incorrect' day value. However, the day changes as expected if I change the start time in the code to 10/3/2016 11:59:45 PM. Am I doing something wrong or is there an alternate more stable way to add time to a Date in VBA?</p>
  
  <p>EDIT:</p>
  
  <p>Excellent workarounds provided by mrbungle and Comintern.  They both achieve the same end result but I like mrbungle's method just a hair better because it avoids altering the value, albeit a negligible amount.</p>
  
  <p>To summarize the alternatives:</p>
  
  <pre><code>Sub test()
      Dim i As Integer
      Dim currentDate As Date
  
      With ThisWorkbook.Sheets(1)
          .Cells.Clear
          .Cells(1, 1) = "DateTime"
          .Cells(1, 2) = "Day"
  
          currentDate = CDate("10/3/2016 11:59:30 PM")
  
          For i = 1 To 10
              .Cells(i + 1, 1) = CStr(currentDate) ' mrbungle's method
              .Cells(i + 1, 1) = currentDate + 0.000000001 ' Comintern's method
              .Cells(i + 1, 2) = DateTime.Day(currentDate)
              currentDate = DateTime.DateAdd("s", 5, currentDate)
          Next i
  
          .Columns(1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
      End With
  End Sub
  </code></pre>