CODEWITHSUNDEEP
excelvba
Justin Moser
Justin Moser

Reputation: 2005

VBA DateAdd not working as expected

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.

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

Output:

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?

EDIT:

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.

To summarize the alternatives:

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

Upvotes: 4

Views: 2220

Answers (2)

Comintern
Comintern

Reputation: 22185

That appears to be an Excel formatting bug. In fact, the VBA values are correct:

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

Output:

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

The moral seems to be that if you're doing date arithmetic in Excel and have to correctly represent midnight, you should add a tiny delta as a workaround:

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

This "tricks" Excel into using the correct date for 12:00 AM.

Upvotes: 2

mrbungle
mrbungle

Reputation: 1931

Not sure if this qualifies as an answer but it does get around the problem. It seems pasting the date as String get's around the issue. And searching around it seems this is a problem with Excel adding dates and moving days.

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

Upvotes: 1

Related Questions