+ Reply to Thread
Results 1 to 33 of 33

getting date and time from different date notations within strings

  1. #1
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    getting date and time from different date notations within strings

    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?
    Attached Files Attached Files

  2. #2
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    MEXICO
    MS-Off Ver
    Excel 2013
    Posts
    212

    Re: 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.

  3. #3
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: getting date and time from different date notations within strings

    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 untill
    Please Login or Register  to view this content.
    for which i get an Invalid Procedure Call Or Argument (Error 5).
    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".
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: getting date and time from different date notations within strings

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: getting date and time from different date notations within strings

    @ Jindon, thank you for your script, but I get an error message at
    Please Login or Register  to view this content.
    : "Type mismatch (Error 13)".

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: getting date and time from different date notations within strings

    no error.........
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: getting date and time from different date notations within strings

    Rotterdam, The Netherlands
    try change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: getting date and time from different date notations within strings

    After i changed the code line i get a Type Mismatch (Error 13) How can i solve this?

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: getting date and time from different date notations within strings

    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.

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi, try this …


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

    PHP Code: 
    Sub Demo1()
       Const 
    "-"" "
             
    = [{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}]
        
    With [A1].CurrentRegion.Columns("A:B")
             
    = .Value
        
    For R& = 2 To .Rows.Count
            
    If IsEmpty(V(R2)) Then
                
    If V(R1Like "*#/[A-Z][a-z][a-z]/####:##:##:##*" Then
                    V
    (R2) = V(R1)
                    
    V(R1) = Split(V(R1), "[")
                    
    V(R1) = Split(V(R1)(UBound(V(R1))))(0)
                         
    P& = InStr(V(R1), ":")
                          
    Split(Left(V(R1), 1), "/")
                    
    V(R1) = W(2) & Application.Match(W(1), M0) & W(0) & Mid(V(R1), 1)
                ElseIf 
    V(R1Like "####-##-##-##:##:##*" Then
                    V
    (R2) = V(R1)
                    
    V(R1) = Left(V(R1), 10) & Left(Mid(V(R1), 12), 12)
                ElseIf 
    V(R1Like "##-##-#### ##:##:##:###" Then
                    V
    (R2) = V(R1)
                    
    V(R1) = Mid(V(R1), 74) & Mid(V(R1), 34) & Left(V(R1), 2) & Mid(V(R1), 119) & "." Mid(V(R1), 21)
                ElseIf 
    V(R1Like "[A-Z][a-z][a-z] ?# ##:##:## *#-*#-#### *" Then
                    V
    (R2) = V(R1)
                    
    V(R1) = Split(Replace(V(R1), "  "S))
                          
    Split(V(R1)(3), D)
                    
    V(R1) = W(2) & W(1) & W(0) & V(R1)(2)
                ElseIf 
    V(R1Like "*[[A-Z][a-z][a-z] [A-Z][a-z][a-z] *# ##:##:##* ####]*" Then
                    V
    (R2) = V(R1)
                    
    V(R1) = Split(Split(Split(V(R1), "]")(0), "[")(1))
                    
    V(R1) = V(R1)(4) & Application.Match(V(R1)(1), M0) & V(R1)(2) & V(31)(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 …

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: getting date and time from different date notations within strings

    The date format in my regional setting is yyyy/m/d, so this one is for d/m/yyyy format.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: getting date and time from different date notations within strings

    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.
    Attached Files Attached Files
    Last edited by MaartenRo; 07-25-2022 at 12:38 AM.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: getting date and time from different date notations within strings

    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.

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: getting date and time from different date notations within strings


    MaartenRo, just update the NumberFormat codeline within my VBA demonstration …
    Last edited by Marc L; 07-25-2022 at 09:24 AM.

  15. #15
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    MEXICO
    MS-Off Ver
    Excel 2013
    Posts
    212

    Re: getting date and time from different date notations within strings

    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.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: getting date and time from different date notations within strings

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

  17. #17
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,521

    Re: getting date and time from different date notations within strings

    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

  18. #18
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: getting date and time from different date notations within strings

    Please try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  19. #19
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    MEXICO
    MS-Off Ver
    Excel 2013
    Posts
    212

    Re: getting date and time from different date notations within strings

    Quote Originally Posted by MaartenRo View Post
    @ 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.

  20. #20
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    getting date and time from different date notations within strings

    Hi,

    I have a column A with different date and time formats. How can i get them equal?
    Attached Files Attached Files

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,265

    Re: Equal different time and date formats

    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.

  22. #22
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: getting date and time from different date notations within strings

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

  23. #23
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: Equal different time and date formats

    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?

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,265

    Re: Equal different time and date formats

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

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,265

    Re: getting date and time from different date notations within strings

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

  26. #26
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: Equal different time and date formats

    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.

  27. #27
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Equal different time and date formats

    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.

  28. #28
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: Equal different time and date formats

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

  29. #29
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Equal different time and date formats

    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.
    Attached Files Attached Files

  30. #30
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: Equal different time and date formats

    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?
    Attached Files Attached Files

  31. #31
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    447

    Re: getting date and time from different date notations within strings

    I started a new thread for functions because the scripts don't work in my excel file. Thanks for trying to solve the problem!

  32. #32
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,265

    Re: getting date and time from different date notations within strings

    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.

  33. #33
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,265

    Re: getting date and time from different date notations within strings

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 05-20-2022, 09:09 AM
  2. [SOLVED] Extract the last or latest date in a row that contains multiple text and date strings
    By maxime45140 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-06-2022, 05:50 AM
  3. adding date and time and subtracting date-time2 from date-time1
    By tinkerbelle in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-22-2018, 10:05 AM
  4. Compare dates from strings and then find first date greater than a date
    By caabdul in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2018, 11:44 PM
  5. Date and Time Formats to give minutes between 2 strings
    By spoursy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-17-2015, 12:04 PM
  6. Replies: 9
    Last Post: 02-15-2015, 07:32 PM
  7. Replies: 3
    Last Post: 12-19-2013, 06:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1