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