Change date format "yyyy/mm/dd" to dd/mm/yyyy

[baskar5353]

hi,

i having one workbook in this few columns contains the "yyyy/mm/dd" format. how to convert this format to dd/mm/yyyy

i want to remove this " symbol and convert to dd/mm/yyyy

how to do in VBA

Regards,
Baskar

 

[MARK858]

If it literally does have the quotes around the date try...

Sub Macro2()
    With Columns("A")
        .Replace What:="/", Replacement:="-", LookAt:=xlPart
        .Replace What:="""", Replacement:="", LookAt:=xlPart
        .NumberFormat = "dd/mm/yyyy"
    End With
End Sub

Change the column to suit

 

[yangc10]

Thanks to Joe4 example, I think this might work for ya. You didn't give us a specific location where the dates are located so I'm putting column B for an example.

Sub FormatDate()


    Dim myFirstCol As String
    Dim myAllCols As Variant
    Dim myLastRow As Long
    Dim i As Long
    Dim myCol As String
    Dim myCell As Range

    Application.ScreenUpdating = False
    
'   Specify columns to apply to
    myAllCols = Array("B")

'   Find last row with data
    myLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
       
'   Loop through all columns
    For i = LBound(myAllCols) To UBound(myAllCols)
        myCol = myAllCols(i)
'   Format column as text
        Columns(myCol).NumberFormat = "@"
'   Loop through cells in column, converting the ones that have values to text in "dd/mm/yyyy" format
        For Each myCell In Range(Cells(1, myCol), Cells(myLastRow, myCol))
            If Len(myCell) > 0 Then myCell = Format(myCell, "dd/mm/yyyy")
        Next myCell
    Next i
    
    Application.ScreenUpdating = True




End Sub

 

[baskar5353]

but some cell it contains in this format 2010-15-06 1.02.30 i want only 2010-15-06 can we trim how can to do it