VBA DateAdd does not work as expected

Question

I am using the DateTime.DateAdd VBA function in Excel to create a date column. Using the following code, the day does not change, as expected, when time progresses until midnight.

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

Conclusion:

• DateTime | Day
• 3/3/2016 11:59:30 PM | 3
• 3/3/2016 11:59:35 PM | 3
• 3/3/2016 11:59:40 PM | 3
• 3/3/2016 11:59:45 PM | 3
• 3/3/2016 11:59:50 PM | 3
• 3/3/2016 11:59:55 PM | 3
• 10/ 3 /2016 12:00:00 AM | 4 (day values ​​do not match!)
• 04/10/2016 12:00:05 AM | 4
• 04/10/2016 12:00:10 AM | 4
• 04/10/2016 12:00:15 AM | 4

. x , "" . , , 10/3/2016 11:59:45 PM. - VBA?

EDIT:

, mrbungle Comintern. , mrbungle , , .

:

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 method
            .Cells(i + 1, 1) = currentDate + 0.000000001 ' Comintern 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

Answer 1

, , . , String . , Excel .

Sub test()
Dim i As Integer
Dim currentDate As Date
Dim currentDate2 As String

With ThisWorkbook.Sheets(1)
    .Cells.Clear
    .Cells(1, 1) = "DateTime"
    .Cells(1, 2) = "Day"

    currentDate = CDate("10/3/2016 11:59:30 PM")
    currentDate2 = currentDate
    For i = 1 To 10
        .Cells(i + 1, 1) = currentDate2
        .Cells(i + 1, 2) = Day(currentDate)
        '.Cells(i + 1, 3) = test
        currentDate = DateTime.DateAdd("s", 5, currentDate)
        currentDate2 = currentDate
    Next i

    .Columns(1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
End Sub

Answer 2

Excel. VBA :

Sub Example()
    Dim i As Integer
    Dim currentDate As Date
    currentDate = CDate("10/3/2016 11:59:30 PM")
    For i = 1 To 10
        Debug.Print currentDate, Day(currentDate)
        currentDate = DateTime.DateAdd("s", 5, currentDate)
    Next i
End Sub

:

10/3/2016 11:59:30 PM        3 
10/3/2016 11:59:35 PM        3 
10/3/2016 11:59:40 PM        3 
10/3/2016 11:59:45 PM        3 
10/3/2016 11:59:50 PM        3 
10/3/2016 11:59:55 PM        3 
10/4/2016      4 
10/4/2016 12:00:05 AM        4 
10/4/2016 12:00:10 AM        4 
10/4/2016 12:00:15 AM        4

, Excel , :

 currentDate = CDate("10/3/2016 11:59:30 PM") + 0.00000000001

"" Excel 12:00 AM.