Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Text to Date Conversion
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2005
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Text to Date Conversion

    I have a column that has an extra "1" at the start. The requirement is to strip that extra character/number and convert the date to a MM/DD/YYYY format.

    Column A
    1140430
    1141124
    1140403

    Column A
    14/04/2030
    14/11/2024
    14/04/2003

    is there a VBA way to do this, preferably without creating any new column and do this manipulation in the same Column A

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    5,008
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Date Conversion

    Code:
    Option Explicit
    
    
    Sub foo()
        Dim lr As Long, i As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To lr
            Range("A" & i) = Right(Range("A" & i), 6)
            Range("A" & i) = Left(Range("A" & i), 2) & "/" & Mid(Range("A" & i), 3, 2) & "/" & Right(Range("A" & i), 2)
        Next i
    End Sub
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Date Conversion

    Code:
    Sub Main()
        Dim r As Range, a, i As Long
        
        Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))
        a = WorksheetFunction.Transpose(r)
        
        On Error Resume Next
        For i = 0 To UBound(a)
            a(i) = Right(a(i), 6)
            Debug.Print i, a(i)
            '1140430, 14/04/2030
            a(i) = DateSerial(Right(a(i), 2), Mid(a(i), 3, 2), Left(a(i), 2))
        Next i
        
        r = WorksheetFunction.Transpose(a)
        r.NumberFormat = "dd/mm/yyyy"
    End Sub

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,149
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Text to Date Conversion

    Just another way:

    Code:
    Sub a1109270a()
    'https://www.mrexcel.com/forum/excel-questions/1109270-text-date-conversion.html
    Dim c As Range, x As String
    Application.ScreenUpdating = False
        For Each c In Range("A1", Cells(Rows.count, "A").End(xlUp))
           
            If IsNumeric(c) And Len(c) = 7 Then
                x = Right(c, 6)
                c = DateSerial(Right(x, 2), Mid(x, 3, 2), Left(x, 2))
            End If
       
        Next
    Range("A1", Cells(Rows.count, "A").End(xlUp)).NumberFormat = "MM/DD/YYYY"
    Application.ScreenUpdating = True
    End Sub
    Last edited by Akuini; Sep 8th, 2019 at 10:45 PM.

  5. #5
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,417
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Text to Date Conversion

    A shorter, faster sub that correctly converts 1140430 into 14/04/2030:
    Code:
    Sub Txt2Date()
        Dim Rng As Range
        Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Rng = Evaluate("INDEX(REPLACE(RIGHT(" & Rng.Address & ",6),5,0,20),0)")
        Rng.TextToColumns , xlDelimited, , , , , , , , , Array(1, 4)
        Rng.NumberFormat = "dd/mm/yyyy"
    End Sub

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,318
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Text to Date Conversion

    Quote Originally Posted by Tetra201 View Post
    A shorter, faster sub that correctly converts 1140430 into 14/04/2030:
    Code:
    Sub Txt2Date()
        Dim Rng As Range
        Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Rng = Evaluate("INDEX(REPLACE(RIGHT(" & Rng.Address & ",6),5,0,20),0)")
        Rng.TextToColumns , xlDelimited, , , , , , , , , Array(1, 4)
        Rng.NumberFormat = "dd/mm/yyyy"
    End Sub
    Here is another way to write a non-looping macro for the OP. I am not sure, but I think VBA/Excel will end up doing less work overall using this code.
    Code:
    Sub Txt2Date()
      Dim Addr As String
      Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
      Range(Addr) = Evaluate(Replace("IF({1},TEXT(RIGHT(REPLACE(RIGHT(@,6),5,0,MID(@,2,2)&20),8),""00\/00\/0000""))", "@", Addr))
      Range(Addr).NumberFormat = "dd/mm/yyyy"
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,417
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Text to Date Conversion

    Quote Originally Posted by Rick Rothstein View Post
    ... I am not sure, but I think VBA/Excel will end up doing less work overall using this code...
    With a 100,000-row dataset in Excel 2010
    Sub from Post # 5: ~0.95 sec
    Sub from Post # 6: ~1.35 sec

  8. #8
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Date Conversion

    This is a mute point since the op has not responded. If the dates are always expected to be year 2000+, posts #5 and #6 are fine. Two digit year dates can be a problem for some routines.
    Last edited by Kenneth Hobson; Sep 9th, 2019 at 11:29 AM.

  9. #9
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Date Conversion

    For giggles, I did a few times tests. The suffix number is the code in that post number.

    Txt2Date3 0.501302083
    Txt2Date5 1.44140625
    Txt2Date6 2.063802083

    Code:
    Sub Avg3RunTimes()
        Dim d As Double, dd As Double
        Dim s As String
        Dim j As Integer, k As Integer
        Dim jj As Integer, kk As Integer
        
        jj = 3    'Number of single runs.
        kk = 3  'Number of replicate runs
        
        For k = 1 To kk 'Replicate runs
            For j = 1 To jj 'Single runs
                s = "Txt2Date" & Choose(j, 3, 5, 6)
                Fill100kRows
                d = Timer
                Application.Run s
                dd = Timer
                Debug.Print s, dd - d & " seconds."
            Next j
        Next k
    End Sub
    
    Sub Fill100kRows()
        Range("A2:A100001").Clear
        Range("A2") = ""
        Range("A3") = 1140499
        Range("A4:A100001") = 1140403
    End Sub

  10. #10
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,417
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Text to Date Conversion

    Here is an even faster sub:
    Code:
    Sub Txt2Date_v2()
        Dim Rng As Range
        Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Rng = Evaluate("INDEX(REPLACE(" & Rng.Address & ",6,0,20),0)")
        Rng.TextToColumns , xlFixedWidth, , , , , , , , , Array(Array(0, 9), Array(1, 4))
        Rng.NumberFormat = "dd/mm/yyyy"
    End Sub
    It takes it ~0.85 sec to process a 100,000-row dataset in Excel 2010.
    Kenneth's sub from Post # 3 was consistently at ~1.1 sec.
    Rick's sub from Post # 6 was consistently at ~1.35 sec.

    Just in case, here is my sub for creating a 100,000-row dataset:
    Code:
    Sub Fill_100K_Rows()
        Dim i As Long
        Application.ScreenUpdating = False
        For i = 1 To 100000
            Range("A" & i) = 1 & _
                Format(Application.RandBetween(1, 28), "00") & _
                Format(Application.RandBetween(1, 12), "00") & _
                Format(Application.RandBetween(0, 99), "00")
        Next i
        Application.ScreenUpdating = True
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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