+ Reply to Thread
Results 1 to 33 of 33

getting date and time from different date notations within strings

Hybrid View

MaartenRo getting date and time from... 07-22-2022, 03:59 AM
Dante Amor Re: getting date and time... 07-22-2022, 11:47 PM
MaartenRo Re: getting date and time... 07-23-2022, 02:42 AM
Dante Amor Re: getting date and time... 07-25-2022, 12:58 PM
MaartenRo Re: getting date and time... 07-26-2022, 12:16 AM
Dante Amor Re: getting date and time... 07-26-2022, 07:55 AM
jindon Re: getting date and time... 07-23-2022, 03:24 AM
MaartenRo Re: getting date and time... 07-23-2022, 06:13 AM
jindon Re: getting date and time... 07-23-2022, 06:22 AM
jindon Re: getting date and time... 07-23-2022, 06:35 AM
MaartenRo Re: getting date and time... 07-24-2022, 01:27 AM
jindon Re: getting date and time... 07-24-2022, 01:53 AM
Marc L Hi, try this … 07-24-2022, 09:05 AM
jindon Re: getting date and time... 07-24-2022, 08:42 PM
MaartenRo Re: getting date and time... 07-25-2022, 12:28 AM
jindon Re: getting date and time... 07-25-2022, 12:54 AM
Marc L Re: getting date and time... 07-25-2022, 09:20 AM
Artik Re: getting date and time... 07-26-2022, 12:22 AM
Bo_Ry Re: getting date and time... 07-26-2022, 04:39 AM
MaartenRo Re: getting date and time... 08-01-2022, 06:29 AM
AliGW Re: getting date and time... 08-01-2022, 06:56 AM
MaartenRo Re: getting date and time... 08-02-2022, 04:33 AM
AliGW Re: getting date and time... 08-02-2022, 04:35 AM
MaartenRo getting date and time from... 08-01-2022, 05:17 AM
AliGW Re: Equal different time and... 08-01-2022, 05:20 AM
MaartenRo Re: Equal different time and... 08-01-2022, 06:37 AM
AliGW Re: Equal different time and... 08-01-2022, 06:53 AM
MaartenRo Re: Equal different time and... 08-01-2022, 07:10 AM
nick.williams Re: Equal different time and... 08-01-2022, 07:25 AM
MaartenRo Re: Equal different time and... 08-01-2022, 07:32 AM
nick.williams Re: Equal different time and... 08-01-2022, 08:32 AM
MaartenRo Re: Equal different time and... 08-02-2022, 04:29 AM
AliGW Re: getting date and time... 08-02-2022, 04:40 AM
  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.

    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

  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
     d2 = Mid(x, n - 4, 10)
    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 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
     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
    Attached Files Attached Files

  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

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

  6. #6
    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.

  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

    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

  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

    @ Jindon, thank you for your script, but I get an error message at
     s = Join(myMonths, "|")
    : "Type mismatch (Error 13)".

  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

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

  10. #10
    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
    myMonths = [text(column(a:l)*28,"mmm")]
    to
    myMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

  11. #11
    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?

  12. #12
    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.

  13. #13
    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 …

  14. #14
    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

  15. #15
    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.

  16. #16
    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.

  17. #17
    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.

  18. #18
    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

  19. #19
    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

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

  20. #20
    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.

  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,259

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

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

  23. #23
    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,259

    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.

  24. #24
    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

  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,259

    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.

  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 the dates and times to be in the same format so i can sort them chronologically. Is there a way to do this?

  27. #27
    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,259

    Re: Equal different time and date formats

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

  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

    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.

  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

    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.

  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

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

  31. #31
    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

  32. #32
    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

  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,259

    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