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.
![]()
Please Login or Register to view this content.
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).![]()
Please Login or Register to view this content.
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".
![]()
Please Login or Register to view this content.
@ Jindon, thank you for your script, but I get an error message at: "Type mismatch (Error 13)".![]()
Please Login or Register to view this content.
no error.........
try changeRotterdam, The Netherlands
to![]()
Please Login or Register to view this content.
![]()
Please Login or Register to view this content.
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.
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).![]()
Please Login or Register to view this content.
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:
![]()
Please Login or Register to view this content.
@ 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..
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
![]()
Please Login or Register to view this content.
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.
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.
@ 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.
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???
You can't ask this in two places: do you want to continue HERE or in the new thread?
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?
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.
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