CODEWITHSUNDEEP
excelvba
Syd
Syd

Reputation: 65

Adding Dates resulting in times past Midnight

I'm attempting to add two Dates in VBA in a large macro, for example:

Public Sub sumDate()
    Dim start As Date
    Dim endTime As Date
    start = #11:31:00 PM#
    endTime = #12:30:00 AM#

    Cells(1, 1) = start + endTime
End Sub

Every time it's running in my large macro, it throws

error 400

and halting. When I run just the code above, I get Runtime error 1004.

Could this have something to do with using the 1904 vs the 1900 date system? My macro runs fine for all times before midnight.

Edit: Changed variables from " to # for clarity.

Upvotes: 1

Views: 231

Answers (2)

braX
braX

Reputation: 11755

You cannot add times together like that if they are on different days.

They need to have a Date value too, or it uses whatever "day 0" is, as you state, Jan 1, 1900 or Jan 1, 1904

Also, adding them together makes little sense, but you would do it like this:

Public Sub sumDate()
    Dim start As Date
    Dim endTime As Date
    start = Date & " 11:31:00 PM"
    endTime = DateAdd("d", 1, Date) & " 12:30:00 AM"

    Cells(1, 1) = start + endTime
End Sub

That will not give you what you want tho. You are probably looking to get what is called a Time Span from them. You can do that like this:

Public Sub sumDate()
    Dim start As Date
    Dim endTime As Date
    start = #11:31:00 PM#
    endTime = #12:30:00 AM#

    Cells(1, 1) = Abs(DateDiff("s", start, endTime))

End Sub

That will give you the number of seconds between those to times ON THE SAME DAY, which is not what you want either.

You want a combination of these things:

Public Sub sumDate()
    Dim start As Date
    Dim endTime As Date
    start = Date & " 11:31:00 PM"
    endTime = DateAdd("d", 1, Date) & " 12:30:00 AM"

    Cells(1, 1) = Abs(DateDiff("s", start, endTime))

End Sub

That will give you the number of seconds.

If you want minutes:

Public Sub sumDate()
    Dim start As Date
    Dim endTime As Date
    start = Date & " 11:31:00 PM"
    endTime = DateAdd("d", 1, Date) & " 12:30:00 AM"

    Cells(1, 1) = Abs(DateDiff("s", start, endTime)) / 60

End Sub

OR

Public Sub sumDate()
    Dim start As Date
    Dim endTime As Date
    start = Date & " 11:31:00 PM"
    endTime = DateAdd("d", 1, Date) & " 12:30:00 AM"

    Cells(1, 1) = Abs(DateDiff("m", start, endTime))

End Sub

but then this is not going to work either if they happen to be on the SAME day.

In short, you NEED to have the ACTUAL DATE ITSELF included in your date/time value if you want DateDiff to work properly.

Adding it manually like I did in my examples is NOT the solution. It just demonstrates that it's not going to work they way you think it is supposed to.

Upvotes: 1

masterwaffle
masterwaffle

Reputation: 136

The variables aren’t the right type. By quoting them, you are giving it the text value, not the time value.

You should enclose the times with # (eg #1:23#)

Upvotes: 0

Related Questions