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.

Please Login or Register  to view this content.

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 …

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Please try

Please Login or Register  to view this content.

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.

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.

@ 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?

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

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.

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