I regularly export a CSV file into Excel, where the dates are in the format MM/DD/YYYY (viz, US format). I need to convert these to YYYYMMDD (Australian format), and am using the code, below. However, what happens is that dates such as 5/1/2010 get converted to YYYYDDMM, whereas dates such as 30/1/2010 get correctly converted to YYYYMMDD. How can I force all dates to be converted to YYYYMMDD?
Public Sub ConvertDatesToYYYYMMDD()
' Converts dates in dd/mm/yyyy, season year etc format to either YYYYMMDD or YYYY as appropriate
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.count).End(xlUp).Row)
With rCell
If IsDate(.Value) Then
.Offset(0, 1).Value = Format(.Value, "yyyymmdd")
Else
.Offset(0, 1).Value = Right(.text, 4)
End If
End With
Next rCell
End Sub
Bookmarks