Extract badly formatted date from a mixed string and convert to YYYY-MM-DD

Question

I had a good search for this but I could not find it, which surprised me. I have an archaic form which must be imported and one of the fields is "Date and location". I have to extract the date from this which may be in a variety of formats depending on the author. The inclusion of location means it can include text of varying lengths.

Examples of data are as follows:

Process 24/03/14 @ 15:30
Archive 24/03/2014 @ 15.45
Matal 24/03/2014 @ 11:30
13.03.14 Falkirk
Process @ 11:21 11/03/14
Intake @ 08;47 20/02/14
Raw Intake Laboratory DOP: 08.01.13 @ 15:30

If it's formatted by Excel's cell format you can use `=TEXT(A1,"yyyy/mm/dd")` if it's a specific pattern of text that's included you can use `Left()`, `Mid()`, and `Right()`, but if it's a string with many other things included you may have to resort to [Regex](http://stackoverflow.com/q/22542834/2521004) — Automate This, Mar 26 '14 at 14:55

score 0 · Answer 1 · edited Mar 27 '14 at 12:38

Here is how I solved the problem:

Public RawTestLocation, testLocation, testDate, autoDate, As String
Sub GetDate()
'Really this just needs the date and test location fields on the report sheet to be separated



RawTestLocation = ActiveSheet.Range("b44").Value

'remove the time which user S adds after an @ sometimes, because it occasionally uses . instead of : for the divider.
If InStr(RawTestLocation, "@") <> 0 Then testLocation = Left(RawTestLocation, InStr(RawTestLocation, "@") - 1)


'Prevent full stop at the end causing an error
If Right(testLocation, 1) = "." Then
    testLocation = Left(testLocation, Len(testLocation) - 1)
End If


'format date in standard format
If testLocation = "" Then testLocation = RawTestLocation
testLocation = Replace(testLocation, ".", "/")



'check for date in middle of string

    Dim StartOfDate As Integer
    LastSlashInDate = InStrRev(testLocation, "/") - 5

    testDate = Mid(testLocation, LastSlashInDate, 10)

    If Not IsDate(testDate) = True Then
        testDate = Mid(testLocation, LastSlashInDate, 8)
    End If

'check for date at beginning of string
    If Not IsDate(testDate) = True Then
        testDate = Right(testLocation, 16)
    End If

    If Not IsDate(testDate) = True Then
        testDate = Left(testLocation, 10)
    End If

    If Not IsDate(testDate) = True Then
        testDate = Left(testLocation, 8)
    End If

'Check for date at end of string
    If Not IsDate(testDate) = True Then
        testDate = Right(testLocation, 10)
    End If

    If Not IsDate(testDate) = True Then
        testDate = Right(testLocation, 8)
    End If


If IsDate(testDate) = True Then
    testDate = CDate(testDate)
    autoDate = True
End If
End Sub

So the sub checks for the date by identifying the last / in the string. If this fails it looks at the end for any date format, then at the beginning. The differing numbers (8, 10 & 16) are used to check for DD/MM/YY, DD/MM/YYYY and DD/MM/YY 00:00 formats.

Extract badly formatted date from a mixed string and convert to YYYY-MM-DD

1 Answers1