Is there an alternative way to get the 2nd occurrence of dateTime in VBA?

Question

How to search for last occurrence of a DateTime in a string using VBA?

For example, given the below sample:

In my sample below, in column A, there are notes that have a DateTime stamp. I need to get the last occurrence of dateTime. If only the notes only contains 1 datetime, then I need to get that. The expected output is in Column B.

I tried to get the dateTime but it is getting the 1st occurrence. See my codes below:

Sub test()
    For x = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(x, 2).Value = Left(Cells(x, 1).Value, 19)
        Cells(x, 2).Select
        Selection.NumberFormat = "yyyy-mm-dd hh:mm:ss"
    Next x
End Sub

Where does your second date starts ? always in a new line ? is it always before a - ? etc....can you precise more please about if there is something permanent in your format that makes us locate the second date ? All that are in the same cell or splitted into different cells ? — JustGreat, Feb 04 '19 at 13:13
You could also have a look at Regular Expressions and use it in VBA. This is a mighty tool and you could [easily extract all data](https://regex101.com/r/Vdd8Wq/2). Also see [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). — Pᴇʜ, Feb 04 '19 at 13:20
2 Questions: 1. Where does the original data come from? 2. Why are some of the entries in different cells while in the 4ᵗʰ cell there are multiple entries in one cell. If the data is orginially from a text file or something I would read that file directly and use RegEx to extract the data from there (which should be easier then). — Pᴇʜ, Feb 04 '19 at 13:37

score 0 · Accepted Answer · answered Feb 04 '19 at 13:35

I believe this should do what you are looking for. You can loop backwards after splitting the contents of the cell, and exit when you find the first valid date.

Option Explicit
Private Function GetLastDate(TextRange As Range) As String
    Dim textToParse         As String: textToParse = TextRange.Value
    Dim textArray           As Variant: textArray = Split(textToParse, vbLf)
    Dim possibleDate        As Variant
    Dim i                   As Long
    Dim j                   As Long
    Const textToSplit = " - "

    'Loop backwards
    For i = UBound(textArray) To LBound(textArray) Step -1
        'A Dash Exists
        If (InStr(1, textArray(i), textToSplit) > 0) Then
            possibleDate = Split(textArray(i), textToSplit)

            'Loop forwards
            For j = LBound(possibleDate) To UBound(possibleDate)
                'If it is a date exit
                If IsDate(Trim(possibleDate(j))) Then
                    GetLastDate = Trim(possibleDate(j))
                    Exit Function
                End If
            Next

        End If
    Next

End Function

Sub Example()
    Dim rng As Range: Set rng = ThisWorkbook.Sheets("Sheet1").Range("a1")
    Debug.Print "the last date is: " & GetLastDate(rng)
End Sub

Thank you. I got the expected output. – hannah Feb 07 '19 at 15:02 — hannah, Feb 07 '19 at 15:02

score 0 · Answer 2 · answered Feb 04 '19 at 14:27

This can also be done using a formula, for example, I have the following in A1

"01/02/2019 testing

04/05/19 test stage


05/09/2019 test3"

And using the following formula

=MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))-2,10)

I get

05/09/2019

Hope this helps.

Is there an alternative way to get the 2nd occurrence of dateTime in VBA?

2 Answers2