Format string to date with double digit day and month using Excel VBA-CodePudding

I have this code VBA that loop through each cell in column and convert the string value to a date and format the date. however it is working perfectly fine with double digit month and day but not single digit. what is cause of problem and how I can make it to always result in double digit i.e "01 - 02 -2021" NOT "1/2/2021"

Sub Date_format()
    
    Dim Cel As Range
    Dim i As Integer
    i = ActiveWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
        
    For Each Cel In ActiveSheet.Range("T2:T" & i)
        If Not Cel.Value = "n/a" Then
            Cel.Value = Format(DateValue(Cel.Value), "dd - mm - yyyy")                
        End If
    Next
    
End Sub

CodePudding user response：

Please, try transforming this part:

        If Not Cel.Value = "n/a" Then
            Cel.Value = Format(DateValue(Cel.Value), "dd - mm - yyyy")                
        End If

as:

        If Not Cel.Value = "n/a" Then
            Cel.NumberFormat = "dd - mm - yyyy"
            Cel.Value = Format(DateValue(Cel.Value), "dd - mm - yyyy")                
        End If

CodePudding user response：

As I mentioned in the comments above, add

.Range("T2:T" & i).NumberFormat = "dd - mm - yyyy"

before the FOR loop.

Here is another way to achieve what you want without using loops.

Code:

Option Explicit

Sub Date_format()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim aCell As Range
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Last row in Col T
        lRow = .Range("T" & .Rows.Count).End(xlUp).Row
        
        With .Range("T2:T" & lRow)
            .NumberFormat = "dd - mm - yyyy"
            
            .Value = ws.Evaluate("index(IF(" & .Address & _
                     "<>""n/a"",DATEVALUE(" & .Address & "),""n/a""),)")
        End With
    End With
End Sub

Screenshot:

Explanation:

This approach uses EVALUATE with INDEX, IF and DATEVALUE to do the conversion without using any loops. For explanation on how it works, please see Convert an entire range to uppercase without looping through all the cells