getting date and time from different date notations within strings

"I have in column A strings including different types of date notations. "

Based on your examples from the file, I was able to identify the following patterns for the date:

1. Mar 16 14:28:36 apadcq_10_monitoring.poort.bela [16/Mar/2019:14:28:36 +0100] GET
2. pr6p0064.proasienst.nl 10.224.4.132.170.43 - - [Thu Nov 12 22:31:09.594 2020] "GET /W4)
3. Feb 4 12:32:26 4-2-2020 11: 32:26;77.24POST;https://mult.aspx?id=C2.10;;;;;
4. 2017-04-19-22:51:55.481000


"and put it in column A, while copying the string to column B?"
I understand that you want the results in column A and the string in column B, but for testing purposes, I'm going to temporarily leave the string in the same column A and the results in column B.
Try the macro and if it meets all the dates, I make the change of columns.

Sub extract_date_time()
  Dim x As String, y As String, z As String, a As String, x4 As String
  Dim d1 As String, d2 As String, d3 As String, d4 As String
  Dim i As Long, n As Long
  Dim c As Range
  Dim ddmmmyyyy As Boolean, yyyymmdd As Boolean, dmyyyy As Boolean, mmmddyyyy As Boolean
  
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    'TIME
    x = c.Value
    n = WorksheetFunction.Search("??:??:??", x)
    z = Mid(x, n - 1, 1)
    If z Like "[0-9]" Then
      n = WorksheetFunction.Search("??:??:??", x, n + 3)
    End If
    
    y = Mid(x, n, 8)
    a = Mid(c.Value, n + 8, 4)
    If Mid(a, 1, 1) = "." And Mid(a, 2, 1) Like "[0-9]" And _
       Mid(a, 3, 1) Like "[0-9]" And Mid(a, 4, 1) Like "[0-9]" Then
      z = y & a
    Else
      z = y & ".000"
    End If
    '
    'DATE
    'dd/mmm/yyyy
    ddmmmyyyy = False
    d1 = ""
    For i = 1 To 12
      n = InStr(1, x, "/" & MonthName(i, True) & "/", vbTextCompare)
      If n > 0 Then
        ddmmmyyyy = True
        d1 = Mid(x, n - 2, 11)
        d1 = Right(d1, 4) & "-" & Format(i, "00") & "-" & Left(d1, 2)
        Exit For
      End If
    Next
    If ddmmmyyyy Then
      c.Offset(, 1).Value = d1 & " " & z
    Else
      'yyyy-mm-dd
      yyyymmdd = False
      d2 = ""
      For i = 1 To 31
        n = InStr(1, x, "-" & Format(i, "00") & "-", vbTextCompare)
        If n > 0 Then
          yyyymmdd = True
          d2 = Mid(x, n - 4, 10)
          Exit For
        End If
      Next
      If yyyymmdd Then
        c.Offset(, 1).Value = d2 & " " & z
      Else
        'd-m-yyyy
        dmyyyy = False
        d3 = ""
        For i = 1 To 12
          n = InStr(1, x, "-" & i & "-", vbTextCompare)
          If n > 0 Then
            dmyyyy = True
            d3 = Trim(Mid(x, n - 2, 10))
            d3 = Right(d3, 4) & "-" & Format(i, "00") & "-" & Format(Replace(Left(d3, 2), "-", ""), "00")
            Exit For
          End If
        Next
        If dmyyyy Then
          c.Offset(, 1).Value = d3 & " " & z
        Else
          ' mmm dd | yyyy
          x4 = Replace(x, z, "")
          x4 = Replace(x4, y, "")
          x4 = Replace(x4, "  ", " ")
          mmmddyyyy = False
          For i = 1 To 12
            n = InStr(1, x4, " " & MonthName(i, True) & " ", vbTextCompare)
            If n > 0 Then
              mmmddyyyy = True
              d4 = Replace(Mid(x4, n + 1, 11), "]", "")
              d4 = Right(d4, 4) & "-" & Format(i, "00") & "-" & Format(Mid(d4, 5, 2), "00")
              Exit For
            End If
          Next
          If mmmddyyyy Then
            c.Offset(, 1).Value = d4 & " " & z
          Else
            c.Offset(, 1).Value = "Not determined" & " " & z
          End If
        End If
      End If
    End If
  Next c
End Sub

I ran it on my source data and the program worked untill

 d2 = Mid(x, n - 4, 10)

for which i get an Invalid Procedure Call Or Argument (Error 5).

I made an adjustment to the code.

Also i am not able to adjust cell properties so that also the miliseconds are displayed. If i set the type as jjjj/mm/dd: uu:mm:ss.000 (j and u in dutch instead of the english Y and h) i get an error message: "the number format entered cannot be used by microsoft".

Change the format of column B to Text

Try this:

Sub extract_date_time()
  Dim x As String, y As String, z As String, a As String, x4 As String
  Dim d1 As String, d2 As String, d3 As String, d4 As String
  Dim i As Long, n As Long
  Dim c As Range
  Dim ddmmmyyyy As Boolean, yyyymmdd As Boolean, dmyyyy As Boolean, mmmddyyyy As Boolean
  
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    'TIME
    x = c.Value
    n = WorksheetFunction.Search("??:??:??", x)
    z = Mid(x, n - 1, 1)
    If z Like "[0-9]" Then
      n = WorksheetFunction.Search("??:??:??", x, n + 3)
    End If
    
    y = Mid(x, n, 8)
    a = Mid(c.Value, n + 8, 4)
    If Mid(a, 1, 1) = "." And Mid(a, 2, 1) Like "[0-9]" And _
       Mid(a, 3, 1) Like "[0-9]" And Mid(a, 4, 1) Like "[0-9]" Then
      z = y & a
    Else
      z = y & ".000"
    End If
    '
    'DATE
    'dd/mmm/yyyy
    ddmmmyyyy = False
    d1 = ""
    For i = 1 To 12
      n = InStr(1, x, "/" & MonthName(i, True) & "/", vbTextCompare)
      If n > 0 Then
        ddmmmyyyy = True
        d1 = Mid(x, n - 2, 11)
        d1 = Right(d1, 4) & "-" & Format(i, "00") & "-" & Left(d1, 2)
        Exit For
      End If
    Next
    If ddmmmyyyy Then
      c.Offset(, 1).Value = d1 & " " & z
    Else
      'yyyy-mm-dd
      yyyymmdd = False
      d2 = ""
      For i = 1 To 31
        n = InStr(1, x, "-" & Format(i, "00") & "-", vbTextCompare)
        If n > 0 Then
          yyyymmdd = True
          If n < 5 Then
            d2 = Mid(x, 1, 10)
          Else
            d2 = Mid(x, n - 4, 10)
          End If
          Exit For
        End If
      Next
      If yyyymmdd Then
        c.Offset(, 1).Value = d2 & " " & z
      Else
        'd-m-yyyy
        dmyyyy = False
        d3 = ""
        For i = 1 To 12
          n = InStr(1, x, "-" & i & "-", vbTextCompare)
          If n > 0 Then
            dmyyyy = True
            d3 = Trim(Mid(x, n - 2, 10))
            d3 = Right(d3, 4) & "-" & Format(i, "00") & "-" & Format(Replace(Left(d3, 2), "-", ""), "00")
            Exit For
          End If
        Next
        If dmyyyy Then
          c.Offset(, 1).Value = d3 & " " & z
        Else
          ' mmm dd | yyyy
          x4 = Replace(x, z, "")
          x4 = Replace(x4, y, "")
          x4 = Replace(x4, "  ", " ")
          mmmddyyyy = False
          For i = 1 To 12
            n = InStr(1, x4, " " & MonthName(i, True) & " ", vbTextCompare)
            If n > 0 Then
              mmmddyyyy = True
              d4 = Replace(Mid(x4, n + 1, 11), "]", "")
              d4 = Right(d4, 4) & "-" & Format(i, "00") & "-" & Format(Mid(d4, 5, 2), "00")
              Exit For
            End If
          Next
          If mmmddyyyy Then
            c.Offset(, 1).Value = d4 & " " & z
          Else
            c.Offset(, 1).Value = "Not determined" & " " & z
          End If
        End If
      End If
    End If
  Next c
End Sub

Originally Posted by MaartenRo

@ Dante Amor: When i run your code i get the message:"the macros in this project are disabled" while i have enabled enable all macro's in my trust center settings..

That's weird, the file works for me. So copy the code and paste it into a module in your file and try again.

Which line?

P.S.
The original code I posted is working fine here outputting all serial dates.
Since I can not replicate error that you are receiving, you need to be very specific about how it is not working.

According to post #3 attachment a VBA demonstration for starters to paste to the Blad11 (Before) worksheet module :

PHP Code: 

Sub Demo1()
   Const D = "-", S = " "
         M = [{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}]
    With [A1].CurrentRegion.Columns("A:B")
         V = .Value
    For R& = 2 To .Rows.Count
        If IsEmpty(V(R, 2)) Then
            If V(R, 1) Like "*#/[A-Z][a-z][a-z]/####:##:##:##*" Then
                V(R, 2) = V(R, 1)
                V(R, 1) = Split(V(R, 1), "[")
                V(R, 1) = Split(V(R, 1)(UBound(V(R, 1))))(0)
                     P& = InStr(V(R, 1), ":")
                      W = Split(Left(V(R, 1), P - 1), "/")
                V(R, 1) = W(2) & D & Application.Match(W(1), M, 0) & D & W(0) & S & Mid(V(R, 1), P + 1)
            ElseIf V(R, 1) Like "####-##-##-##:##:##*" Then
                V(R, 2) = V(R, 1)
                V(R, 1) = Left(V(R, 1), 10) & S & Left(Mid(V(R, 1), 12), 12)
            ElseIf V(R, 1) Like "##-##-#### ##:##:##:###" Then
                V(R, 2) = V(R, 1)
                V(R, 1) = Mid(V(R, 1), 7, 4) & Mid(V(R, 1), 3, 4) & Left(V(R, 1), 2) & Mid(V(R, 1), 11, 9) & "." & Mid(V(R, 1), 21)
            ElseIf V(R, 1) Like "[A-Z][a-z][a-z] ?# ##:##:## *#-*#-#### *" Then
                V(R, 2) = V(R, 1)
                V(R, 1) = Split(Replace(V(R, 1), "  ", S))
                      W = Split(V(R, 1)(3), D)
                V(R, 1) = W(2) & D & W(1) & D & W(0) & S & V(R, 1)(2)
            ElseIf V(R, 1) Like "*[[A-Z][a-z][a-z] [A-Z][a-z][a-z] *# ##:##:##* ####]*" Then
                V(R, 2) = V(R, 1)
                V(R, 1) = Split(Split(Split(V(R, 1), "]")(0), "[")(1))
                V(R, 1) = V(R, 1)(4) & D & Application.Match(V(R, 1)(1), M, 0) & D & V(R, 1)(2) & S & V(3, 1)(3)
            End If
        End If
    Next
       .Item(1).NumberFormat = "yyyy-mm-dd hh:mm:ss.000"
       .Value = V
       .AutoFit
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Thanks, both scripts from Jindon and Marc L. are working! But i get for both scripts different time and date notations. Is it possible to change the script so that the date and time notations are all in "yyyy-dd-mm hh:mm:ss.000" format? Somehow the formatting is not applied to the output.

As I can not test the code with the different date format.(regional settings)

Enter any date like Feb 25, so that I can identify Year, Month snd Day, in any blank cell and read what is in the FORMULA bar. (not the cell)

mycode is inserting the result like d/m/yyyy hh:mm:ss.000,, if you can not change the cell format, they are just text, not serial dates.

MaartenRo, just update the NumberFormat codeline within my VBA demonstration …

Please try

Sub getDT()
Dim a, t, i&
With Range("a2", Range("a" & Rows.Count).End(xlUp))
    a = .Value
    With CreateObject("VBScript.RegExp")
        For i = 1 To UBound(a, 1)
            .Pattern = "\D(\d{2}:\d{2}:\d{2}[.:;]?\d{0,6})"
            Set t = .Execute(a(i, 1))(0)
            a(i, 1) = Application.Trim(Replace(a(i, 1), t.Value, " "))
            .Pattern = "\d+-\d+-\d+|[A-Z][a-y]{2} \d+\ \d{4}|\d+/[A-Z][a-y]{2}/\d+"
             a(i, 1) = DateValue(.Execute(a(i, 1))(0)) & " " & t.submatches(0)
        Next
    End With
    .Columns(2).Value = a
    .Columns(2).NumberFormat = "yyyy-mm-dd hh:mm:ss.000"
End With
End Sub

@ Dante Armor, thanks for your advice, i can run the code now.
The dates in the formats dd-mm-yyyy hh:mm:nn,000 (like e.g. 1-10-2020 08:14:54:160) are not converted to the yyyy-mm-dd hh:nn:ss,000 notations.
Is there a way to get the date time notations right?

You can't ask this in two places: do you want to continue HERE or in the new thread?

Don't do that again - if something isn't working, simply post back to the original thread and wait patiently.

Expect duplcate threads to be closed in future, as per the forum rules.

Thanks for your co-operation.

In what format do you want them? None of them are actually dates - they are just text - and they won't convert uring text to columns.

Fine - but WHAT FORMAT do you want them in???

Everything is now in THIS thread, which has been moved to the Formulas & Functions section.