Excel VBA convert Date with time

Question

I have 2 cells with time data that format as follows:

"A1" = Sep 01 2018 00:01:33.707 
"A2" = Sep 01 2018 00:01:49.917

I need to create a button and method within excel VBA that will set "A3" cell to true if the time "A2" is more than "A1" by 90 seconds. This is what i have so far but it does not work:

Sub Macro2()
    Dim str1 As String, str2 As String

    With Worksheets("sheet5")

        str1 = .Cells(1, "A").Text
        str2 = .Cells(2, "A").Text

        'greater than 90m seconds in A3
        .Cells(3, "A") = CBool(Abs((DateValue(Left(str1, 6) & "," & Mid(str1, 7, 5)) + _
                                    TimeValue(Mid(str1, 13, 8)) + TimeSerial(0, 0, 1) * CDbl(Right(str1, 4))) - _
                                   (DateValue(Left(str2, 6) & "," & Mid(str2, 7, 5)) + _
                                   TimeValue(Mid(str2, 13, 8)) + TimeSerial(0, 0, 1) * CDbl(Right(str2, 4)))) > _
                                   TimeSerial(0, 0, 90))
        'actual absolute difference in A4
        .Cells(4, "A") = Abs((DateValue(Left(str1, 6) & "," & Mid(str1, 7, 5)) + _
                                    TimeValue(Mid(str1, 13, 8)) + TimeSerial(0, 0, 1) * CDbl(Right(str1, 4))) - _
                                   (DateValue(Left(str2, 6) & "," & Mid(str2, 7, 5)) + _
                                    TimeValue(Mid(str2, 13, 8)) + TimeSerial(0, 0, 1) * CDbl(Right(str2, 4))))
    End With End Sub

The above gives error because Date functions works with system Locale, which in my case is Hebrew, while the Data is in English.

Another way that could help is to convert all the column "A" (which holds the dates) to a system local date that can be used with Date and time functions on VBA (don't know how to do that).

Please help

Are the values in A1 and A2 real dates or strings that look like a date? — FunThomas
– FunThomas, Commented Nov 13, 2018 at 10:05
@FunThomas they are real dates from another system that exported as Strings — user1610208
– user1610208, Commented Nov 13, 2018 at 10:26

FunThomas · Accepted Answer · 2018-11-14 08:52:11Z

1

I have split your task into 3 functions.

a) a helper function converts the 3 characters of the month into an integer. It looks a little clumsy, there might be other approaches but the advantage of using a large Select Case is it is easy to understand and easy to adapt if month names in a different language arise:

Function getMonthFromName(monthName As String) As Integer

    Select Case UCase(monthName)
        Case "JAN": getMonthFromName = 1
        Case "FEB": getMonthFromName = 2
        Case "MAR": getMonthFromName = 3
        Case "APR": getMonthFromName = 4
        (...)
        Case "SEP": getMonthFromName = 9
        (...)
    End Select

End Function

b) a function that converts the string into a date. It assumes the date format in the form you provided, but it is easily adapted if the format changes (for simplicity, the seconds are rounded)

Function GetDateFromString(dt As String) As Date

    Dim tokens() As String
    tokens = Split(Replace(dt, ":", " "), " ")

    Dim day As Integer, month As Integer, year As Integer
    month = getMonthFromName(CStr(tokens(0)))
    day = Val(tokens(1))
    year = Val(tokens(2))

    Dim hour As Integer, minute As Integer, second As Double
    hour = Val(tokens(3))
    minute = Val(tokens(4))
    second = Round(Val(tokens(5)), 0)

    GetDateFromString = DateSerial(year, month, day) + TimeSerial(hour, minute, second)
End Function

c) A function that calculated the difference of the 2 dates in seconds. A date in VBA (and many other environments) is stored as Double, where the Date-Part is the integer part and the date is the remainder. This makes it easy to calculate with Date values.

Function DateDiffInSeconds(d1 As String, d2 As String) As Long
    Dim diff As Double
    diff = GetDateFromString(d2) - GetDateFromString(d1)
    DateDiffInSeconds = diff * 24 * 60 * 60
End Function

Update to deal with milliseconds: Change the GetDateFromString-function. In that case, DateDiffInSeconds should return a double rather than a long.

Function GetDateFromString(dt As String) As Date

    Const MillSecPerHour As Long = 24& * 60 * 60 * 1000

    Dim tokens() As String
    tokens = Split(Replace(Replace(dt, ".", " "), ":", " "), " ")

    Dim day As Integer, month As Integer, year As Integer
    month = getMonthFromName(CStr(tokens(0)))
    day = Val(tokens(1))
    year = Val(tokens(2))

    Dim hour As Integer, minute As Integer, second As Integer, milli As Integer
    hour = Val(tokens(3))
    minute = Val(tokens(4))
    second = Val(tokens(5))
    milli = Val(tokens(6))

    GetDateFromString = DateSerial(year, month, day) _
                      + TimeSerial(hour, minute, second) _
                      + milli / MillSecPerHour
End Function

edited Nov 14, 2018 at 8:52

answered Nov 13, 2018 at 13:39

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1610208 Over a year ago

thank you for your excellent complete solution, it works great! I will try to make the first function faster by using an array with months names and return the index of the correct month. Is there away to include the milliseconds into calculations as well?

FunThomas Over a year ago

I have updated my answer to show a way to deal with milliseconds.

FunThomas Over a year ago

If it is only for speed: Forget about changing it to an array: This function is so fast that you never will be able to measure it.

Dominique · Accepted Answer · 2018-11-13 11:02:21Z

0

For your information, I've done what you are doing in a slightly different (and easier) way:

In cell B2, I put the value 13/11/2018 11:44:00.
In cell B3, I put the value 13/11/2018 11:45:01.
(For both cells, the cell formatting has been set to d/mm/jjjj u:mm:ss).

In another cell, I put following formula:

=IF((B3-B2)*86400>90;TRUE;FALSE)

The formula is based on the idea that a datetime value is set, based on the idea that one day equals 1, and there are 86400 seconds in one day.

Like this, you can calculate time differences without needing VBA.

answered Nov 13, 2018 at 11:02

Dominique

17.7k20 gold badges81 silver badges161 bronze badges

2 Comments

user1610208 Over a year ago

Thanks, but i really need it on VBA since it's going to be part of a bigger macro. Also, i see that you changed the values of the Cells, i don't have an option to change it since they are imported as is, this is why i needed a convert solution...

Dominique Over a year ago

@user1610208: my idea is based on the automatic conversion of dates. I've just entered "01/04/2014" in an Excel sheet, and it gets automatically converted to a date. If you can find a way to convert your cell values automatically to dates, your problem might be solved. Can you get rid of the downvote of my answer?

Michal · Accepted Answer · 2018-11-13 11:23:38Z

0

I think you are over-complicating it, try this to get an idea how to do it:

Sub Macro2()
    Dim str1 As String, str2 As String

    With Worksheets("sheet5")

        .Range("b1:e1") = Split(Range("A1"), " ")
        .Range("B2:e2") = Split(Range("A2"), " ")


End Sub

edited Nov 13, 2018 at 11:23

answered Nov 13, 2018 at 10:27

Michal

7,6894 gold badges30 silver badges47 bronze badges

5 Comments

user1610208 Over a year ago

this does not take into considuration the Date itself (month, day or year), i need to include them as well...

Michal Over a year ago

No it didn't. Sorry, but if you split your string in the suggested way, you can recreate the date using a lookup for the month value.

user1610208 Over a year ago

can you put up a complete solution please?

Michal Over a year ago

Use something like =DATE(D1,MATCH(B1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),C1) to convert the string into a date, add the time value and the rest should be simple.

user1610208 Over a year ago

I need it on VBA script since i got tons of sheets

Dy.Lee · Accepted Answer · 2018-11-13 14:24:53Z

0

Use UDF.

Sub Macro2()
    Dim str1 As String, str2 As String
    Dim mySecond As Double, t1 As Double, t2 As Double, t3 As Double
    mySecond = TimeSerial(0, 0, 90)

    With Worksheets("sheet5")
        str1 = .Cells(1, "A").Text
        str2 = .Cells(2, "A").Text
        t1 = ConvertTime(str1)
        t2 = ConvertTime(str2)
        t3 = t2 - t1
        .Cells(3, "a") = Abs(t3) >= mySecond
    End With

End Sub
Function ConvertTime(s As String)
    Dim vS
    vS = Split(s, " ")
    ConvertTime = DateValue(vS(0) & "-" & vS(1) & "-" & vS(2)) + TimeValue(Split(vS(3), ".")(0))
End Function

edited Nov 13, 2018 at 14:24

answered Nov 13, 2018 at 13:53

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Collectives™ on Stack Overflow

Excel VBA convert Date with time

4 Answers 4

3 Comments

2 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related