Hi,
I have in column A strings including different types of date notations. Is it possible with a macro or function to get each date and time notation out of the string and put it in column A, while copying the string to column B?
Hi,
I have in column A strings including different types of date notations. Is it possible with a macro or function to get each date and time notation out of the string and put it in column A, while copying the string to column B?
"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
Visit : Excel & Macro
https://www.youtube.com/@CursosDeExcelyMacros
Thank you very much.. Really interesting to see the way you made the script!
I ran it on my source data and the program worked untillfor which i get an Invalid Procedure Call Or Argument (Error 5).![]()
d2 = Mid(x, n - 4, 10)
I added the string on which the error message was displayed in the uploaded file.
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".
I made an adjustment to the code.I ran it on my source data and the program worked untillfor which i get an Invalid Procedure Call Or Argument (Error 5).![]()
d2 = Mid(x, n - 4, 10)
Change the format of column B to TextAlso 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".
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
@ 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..
![]()
Sub test() Dim a, i As Long, myMonths, s As String, sm As Object, myDate myMonths = [text(column(a:l)*28,"mmm")] s = Join(myMonths, "|") With Range("a2", Range("a" & Rows.Count).End(xlUp)) a = .Value With CreateObject("VBScript.RegExp") For i = 1 To UBound(a, 1) .Pattern = "\b(\d{1,2})[ /-](" & s & ")([ /-](\d{4}))?[: ]*(\d{2}:\d{2}:\d{2}(\.\d{1,3})?)?" If .test(a(i, 1)) Then Set sm = .Execute(a(i, 1))(0).submatches myDate = DateSerial(sm(3), Application.Match(sm(1), myMonths, 0), sm(0)) & " " & sm(4) End If .Pattern = "\b(" & s & ")[ /-]+(\d{1,2})[ /:-]*(\d{2}:\d{2}:\d{2}\.?\d{1,3}?)[ /:-]*(\d{4})" If .test(a(i, 1)) Then Set sm = .Execute(a(i, 1))(0).submatches myDate = DateSerial(sm(3), Application.Match(sm(0), myMonths, 0), sm(1)) & " " & sm(2) End If .Pattern = "\b(\d{1,2})[ /-]+(\d{1,2})[ /-]+(\d{4})[ /-]+(\d{2}: ?\d{2}: ?\d{2})[:.]?(\d{1,3})?" If .test(a(i, 1)) Then Set sm = .Execute(a(i, 1))(0).submatches myDate = DateSerial(sm(2), sm(0), sm(1)) & " " & Replace(sm(3), " ", "") & "." & sm(4) End If .Pattern = "\b(\d{4})[ /-]+(\d{1,2})[ /-]+(\d{1,2})[ /-]+(\d{2}: ?\d{2}: ?\d{2})[:.]?(\d{1,3})?" If .test(a(i, 1)) Then Set sm = .Execute(a(i, 1))(0).submatches myDate = DateSerial(sm(0), sm(1), sm(2)) & " " & Replace(sm(3), " ", "") & "." & sm(4) End If a(i, 1) = myDate: myDate = "" Next End With .Columns(2).Value = a .Columns(2).NumberFormat = "yyyy-dd-mm hh:mm:ss.000" End With End Sub
@ Jindon, thank you for your script, but I get an error message at: "Type mismatch (Error 13)".![]()
s = Join(myMonths, "|")
no error.........
try changeRotterdam, The Netherlands
to![]()
myMonths = [text(column(a:l)*28,"mmm")]
![]()
myMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
After i changed the code line i get a Type Mismatch (Error 13) How can i solve this?
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.
Last edited by jindon; 07-24-2022 at 07:04 AM.
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 » ! ◄ ◄
Last edited by Marc L; 07-24-2022 at 09:50 AM. Reason: format optimization …
The date format in my regional setting is yyyy/m/d, so this one is for d/m/yyyy format.
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.
Last edited by MaartenRo; 07-25-2022 at 12:38 AM.
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.
Last edited by jindon; 07-25-2022 at 01:38 AM.
MaartenRo, just update the NumberFormat codeline within my VBA demonstration …
Last edited by Marc L; 07-25-2022 at 09:24 AM.
In the file manager, under right click, select Properties. Under the General tab, at the bottom, there should be a CheckBox "Unblock" (or something similar) select it and OK. Now open the file in Excel.
Artik
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?
Last edited by MaartenRo; 08-01-2022 at 06:33 AM.
You can't ask this in two places: do you want to continue HERE or in the new thread?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I started a new thread for functions because the scripts don't work in my excel file. Thanks for trying to solve the problem!
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.
Hi,
I have a column A with different date and time formats. How can i get them equal?
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.
I would like the dates and times to be in the same format so i can sort them chronologically. Is there a way to do this?
Fine - but WHAT FORMAT do you want them in???
i would like them in the format jjjj-mm-dd hh:nn:ss,000. For example 2017-04-19 22:50:53:199. I can fill this in manually in cel properties > adapted(translated from dutch) > type.
Are there any other formats apart from the ones in your sample file? Or do you not know all the formats in advance e.g. if they are manual input.
@ Nick all the formats are in the sample file in colomn B. So ik would like
2017-04-29 16:18:01:488
10-12-2019 02:22:09:065
to be displayed both in the jjjj-mm-dd hh:nn:ss,000 format.
For the 03/Aug/2018:09:02:21 format i already have a function that can put it in 2018-08-03 09:02:21.000
..
This should work for all 3 formats, or it does for me at least:
=DATEVALUE(LEFT(A2,11))+IFERROR(TIMEVALUE(MID(A2,12,8))+RIGHT(A2,3)/24/60/60/1000,TIMEVALUE(RIGHT(A2,8)))
I have attached a file as the formats might be different in your region. I don't know if DATEVALUE might work differently too in which case you would need something more complicated.
That works! Thanks.. I would also like a formula that adds a zero when the day or month is displayed by only one digit and deletes the stripe between the date and time when there's a stripe between these values. So that 1-10-2020 08:14:54:496 becomes 01-10-2020 08:14:54:496 and 2017-04-19-22:02:27.639000 becomes 2017-04-19 22:02:27,639000.
Is that possible?
Everything is now in THIS thread, which has been moved to the Formulas & Functions section.
Last edited by AliGW; 08-02-2022 at 04:42 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks