Excel VBA - Date Substitute

Question

I am about to substitute data format in entire row by using VBA in MS Excel from text format from YY-MM-DD into DD-MM-YY. I tried to remove apostrophe from the beginning but then date 21-04-02 is recognized as 21.04.2002 instead of 02.04.2021, so need to substitute characters within each ell in entire row.

In below code I need to add replacing part for each cell

Sub DateSub()
   Dim strInput As String, strOutput As String
   Dim LastRowcheck As Long, n1 As Long, rowschecktodelete As Long

  LastRowcheck = Sheets("T1").Range("B" & Rows.Count).End(xlUp).Row

  For n1 = 2 To LastRowcheck
    With Worksheets("Sheet1").Cells(n1, 2)
     'HERE SHOULD BE REPLACING PART
        Sheets("T1").Cells(n1, 2).Select
        Selection.NumberFormat = "yy-mm-dd"
   End With
  Next n1
    Application.DisplayAlerts = False

End Sub

A formula can solve this. Let's say your date in `A1` = `'21-04-02`. Put the formula `=DATE(2000+LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))` in cell `B1`. If you still want vba then use the same logic there — Siddharth Rout, Apr 23 '21 at 13:11
I am sure that will work, but I am the VBA newbie and I miss something to make it work properly, I would like to replace this way each cell in the column B. Sheets("T1").Cells(n1, 2) = DATE(2000+LEFT(nl,2),MID(A1,4,2),RIGHT(nl,2)) — stasser, Apr 23 '21 at 13:24

score 0 · Accepted Answer · answered Apr 23 '21 at 15:47

If you want VBA then you can convert the entire range in one go without looping as shown below

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim sAddr As String
    Dim lRow As Long
    
    Set ws = Worksheets("Sheet1")
    
    With ws
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row
        
        Set rng = Range("T1:T" & lRow)
        sAddr = rng.Address

        rng = Evaluate("index(DATE(2000+LEFT(" & sAddr & _
                              ",2),MID(" & sAddr & _
                              ",4,2),RIGHT(" & sAddr & _
                              ",2)),)")
                              
        rng.NumberFormat = "DD-MM-YY"
    End With
End Sub

To understand how this works, please see Convert an entire range to uppercase without looping through all the cells

In Action

score 0 · Answer 2 · answered Apr 23 '21 at 19:54

You can use the Text to Columns method.

For example:

Option Explicit
Sub convDates()
    Const convCol As Long = 1
    Dim WS As Worksheet, Rng As Range
    
'change sheetName, convCol and destination to your specifications if this works for you.
    
Set WS = Worksheets("sheet5")
With WS
    Set Rng = Range(.Cells(1, convCol), .Cells(.Rows.Count, convCol).End(xlUp))
    Rng.TextToColumns _
        Destination:=.Cells(1, convCol + 1), _
        DataType:=xlDelimited, _
        textqualifier:=xlTextQualifierDoubleQuote, _
        Consecutivedelimiter:=True, _
        Tab:=False, _
        semicolon:=False, _
        Space:=False, _
        other:=False, _
        fieldinfo:=Array(1, xlYMDFormat)
End With
End Sub

In the code above convCol is the column you wish to convert (col A in this example) and also note that we are writing the results one column to the left (see the Destination:= argument.

If you are satisfied with how this is working, you can change convCol to your column of interest (probably column B or 2) and, by removing the +1 from the Destination:= parameter, overwrite the original data.

Excel VBA - Date Substitute

2 Answers2