Inconsistent CSV date and cash formats generating errors

[Ochimus]

Apologies that this is "long winded" compared to the typical query on here.

Attached CSV file "Mixedbatch Tesr" contains extracts from three different CSV files sent by End User.
Rows 2 - 5 have date and time as "YYYY-MM-DD HH:MM:SS" and money as "£xxx.xx"
Rows 6 - 9 change the date and time to "YYYY-MM-DD HH:MM", and have symbol "œ" in place of "£"
Rows 10 - 13 show the date as "DD/MM/YYYY HH:MM", and the "œ" symbol in place of the "£"
Almost the only permutation missing so far is a US-style "MM/DD/YYYY"!

The Excel file attached opens File Manager, enables the User to select the CSV file, and converts the Text to Columns, all of which works perfectly.

Problem is the next stage. I needs it to sort into Col A date order, then name the sheet as the "Min and Max" dates in Col A (so with this sample it would be "Appts 10 April 2016 - 14 May 2016")

But the "mixed" date formats cause programme to generate "#Value", it can't sort properly, and can't find the Min and Max.

Having now rewritten the Code each month to overcome the changed format, I am hoping someone can show me the coding that will "standardise" whatever Date and Time format turns up on the CSV download?

Option Explicit

Sub ADDMONTH()

    Dim a As Long
    Dim CSV As String, myFilepath As String
    Dim Sheet1 As Worksheet
 
    Application.ScreenUpdating = False

'Convert CSV file to Excel and add to existing Database
    
        myFilepath = Application.GetOpenFilename(Filefilter:=CSV)
    
        If myFilepath = "False" Then Exit Sub

        Workbooks.Open Filename:=myFilepath
    
'Convert to columns and set as Autofit
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1)), TrailingMinusNumbers:= _
        True
        Cells.Select
        Selection.Columns.AutoFit
          
'Sort into date order
        With ActiveSheet
        a = .Cells(.Rows.Count, "A").End(xlUp).Row
        If a < 2 Then Exit Sub
        Columns("A:C").Select
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range( _
        "A2:A" & a), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
        End With
    
        With ActiveSheet.Sort
            .SetRange Range("A1:C" & a)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
            End With
            
'Name sheet with first and last dates in Col A
        ActiveSheet.Name = "Appts ""&TEXT(MIN(C[-26]),""dd mmm yyyy"")&"" - ""&TEXT(MAX(C[-26]),""dd mmm yyyy"")"
        ActiveSheet.Name = Range("AA1").Value
        Range("AA1").ClearContents
        ActiveSheet.Move Before:=Workbooks("TESTMOVE.xlsM").Sheets(2)
   
        Windows("TESTMOVE.xlsm").Activate
        Sheets("DATABASE").Select
        Range("A1").Select
    
       
        End Sub

Needless to say, the End User is adamant they "changed nothing" over the three downloads, and completely uninterested in doing anything their end.

All suggestions, pointers and improvements welcomed gratefully as usual.

Ochimus