bobkasterop
New Member
- Joined
- May 10, 2018
- Messages
- 6
Hey i have a column full of dates that i need to arrange in the format dd-mm-yyyy.
I do this by this code:
Sub ChangeFormat()
With ActiveSheet.UsedRange.Columns("D").Cells
.TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
.NumberFormat = "dd-mm-yyyy" 'change to any date-based number format you prefer the cells to display
End With
End Sub
the problem is that this makes it assume that all dates in the column is of the format YMD, but it is not.
some of my dates in the sheet is of format dd-mm-yy. how do i make it check if it is already in the right formate and then only change the format if it is not?
I do this by this code:
Sub ChangeFormat()
With ActiveSheet.UsedRange.Columns("D").Cells
.TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
.NumberFormat = "dd-mm-yyyy" 'change to any date-based number format you prefer the cells to display
End With
End Sub
the problem is that this makes it assume that all dates in the column is of the format YMD, but it is not.
some of my dates in the sheet is of format dd-mm-yy. how do i make it check if it is already in the right formate and then only change the format if it is not?
