
Hope this helps

A: All the above solutions - including r0berts - were unsuccessful, but found this bizarre solution which turned out to be the fastest fix.
I was trying to sort "dates" on a downloaded spreadsheet of account transactions.  
This had been downloaded via CHROME browser.  No amount of manipulation of the "dates" would get them recognised as old-to-new sortable.
But, then I downloaded via INTERNET EXPLORER browser - amazing - I could sort instantly without touching a column.
I cannot explain why different browsers affect the formatting of data, except that it clearly did and was a very fast fix.

A: SHARING A LITTLE LONG BUT MUCH EASIER SOLUTION TO SUBJECT.....
No need to run macros or geeky stuff....simple ms excel formulae and editing.
mixed dates excel snapshot:



*

*The date is in the mixed format.  

*Therefore, to make a symmetrical
date format, extra columns have been created converting that'mixed
format' date column to TEXT.  

*From that text we have identified the
positions of "/" and middle text has  been extracted determining
date, month, year using MID formula.  

*Those which were originally dates, formula ended with ERROR / #VALUE result. - Finally, from the string we created - we have converted those to dates by DATE formula.

*#VALUE were picked as it is by IFERROR added to DATE formula & the column was formatted as required (here, dd/mmm/yy)


* REFER THE SNAPSHOT OF EXCEL SHEET ABOVE (= mixed dates excel snapshot) *

A: My solution in the UK - I had my dates with dots in them like this:
03.01.17

I solved this with the following:


*

*Highlight the whole column. 

*Go to find and select/ replace.

*I replaced all the full stops with middle dash  eg   03.01.17   03-01-17.

*Keep the column highlighted.

*Format cells, number tab select date.

*Use the Type 14-03-12 (essential for mine to have the middle dash)

*Locale English (United Kingdom)


When sorting the column all dates for the year are sorted.

A: I tried the various suggestions, but found the easiest solution for me was as follows...
See Images 1 and 2 for the example... (note - some fields were hidden intentionally as they do not contribute to the example).  I hope this helps...


*

*Field C - a mixed format that drove me absolutely crazy. This is how the data came directly from the database and had no extra spaces or crazy characters. When displaying it as a text for example, the 01/01/2016 displayed as a '42504' type value, intermixed with the 04/15/2006 which showed as is.

*Field F -  where I obtained the length of field C (the LEN formula would be a length of 5 of 10 depending on the date format). The field is General format for simplicity.

*Field G - obtaining the month component of the mixed date - based on the length result in field F (5 or 10), I either obtain the month from the date value in field C, or obtain the month characters in the string.

*Field H - obtaining the day component of the mixed date - based on the length result in field F (5 or 10), I either obtain the day from the date value in field C, or obtain the day characters in the string.
I can do this for the year as well, then create a date from the three components that is consistent. Otherwise, I can use the individual day, month, and year values in my analysis.
Image 1: basic spreadsheet showing values and field names
Image 2: spreadsheet showing formulas matching explanation above
I hope this helps.

A: Select dates and run this code over it..
On Error Resume Next
    For Each xcell In Selection            
        xcell.Value = CDate(xcell.Value)     
    Next xcell                              
End Sub


A: This happens all the time when exchanging date data between locales in Excel.
If you have control over the VBA program that exports the data, I suggest exporting the number representing the date instead of the date itself. The number uniquely correlates with a single date, regardless of formatting and locale.
For example, instead of the CSV file showing 24/09/2010 it will show 40445.
In the export loop when you hold each cell, if it's a date, convert to number using CLng(myDateValue). This is an example of my loop running through all rows of a table and exporting to CSV (note I also replace commas in strings with a  tag that I strip when importing, but you may not need this):
arr = Worksheets(strSheetName).Range(strTableName & "[#Data]").Value

    For i = LBound(arr, 1) To UBound(arr, 1)
        strLine = ""
        For j = LBound(arr, 2) To UBound(arr, 2) - 1
            varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
            strLine = strLine & varCurrentValue & ","
        Next j
        'Last column - not adding comma
        varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
        strLine = strLine & varCurrentValue

        strLine = Replace(strLine, vbCrLf, "<vbCrLf>")'replace all vbCrLf with tag - to avoid new line in output file
        strLine = Replace(strLine, vbLf, "<vbLf>")'replace all vbLf with tag - to avoid new line in output file
        Print #intFileHandle, strLine
    Next i


A: I was not having any luck with all the above suggestions - came up with a very simple solution that works like a charm. Paste the data into Google Sheets, highlight the date column, click on format, then number, and number once again to change it to number format. I then copy and paste the data into the Excel spreadsheet, click on the dates, then format cells to date. Very quick. Hope this helps.

A: This problem was driving me crazy, then I stumbled on an easy fix.  At least it worked for my data. It is easy to do and to remember.  But I do not know why this works but changing types as indicated above does not.
1. Select the column(s) with the dates
2. Search for a year in your dates, for example 2015.  Select Replace All with the same year, 2015.
3. Repeat for each year.
This changes the types to actual dates, year by year.
This is still cumbersome if you have many years in your data. Faster is to search for and replace 201 with 201 (to replace 2010 through 2019); replace 200 with 200 (to replace 2000 through 2009); and so forth.  

A: No answers here have suggested Excel Power Query. I had a DateTime value (e.g. 2021-02-25T00:35:54.497Z) that excel couldn't interpret correctly. I needed both date and time. In a new Excel workbook, I used the "Get Data > From CSV" option to load into Power Query. Then loaded the results to excel sheet and the date values are recognized correctly. Note that type datetime vs type datetimezone makes a difference. I had to datetimezone to get the correct result.
Power Query - automated script from "Excel > Get Data" GUI
let
    Source = Csv.Document(File.Contents("C:\myFolder\DataSet_RefreshHistory.csv"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"refreshType", type text}, {"startTime", type datetimezone}, {"endTime", type datetimezone}, {"status", type text}, {"ReportName", type text}, {"WorkspaceId", type text}, {"serviceExceptionJson", type text}})
in
    #"Changed Type"

GET DATA

BEFORE and AFTER


A: I usually just create an extra column for sorting or totaling purposes so use year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).
That will provide a yyyymmdd result that can be sorted. Using the