Argthtjtr

Select all the column and go to Locate and Replace and just replace "/" with /.

I had the exact same issue with dates in excel. The format matched the requirements for a date in excel, yet without editing each cell it would not recognise the date, and when you are dealing with thousands of rows it is not practical to manually edit each cell. Solution is to run a find and replace on the row of dates where i replaced the hypen with a hyphen. Excel runs through the column replacing like with like but the resulting factor is that it now recongnises the dates! FIXED!

This may not be relevant to the original questioner, but it may help someone else who is unable to sort a column of dates.

I found that Excel would not recognise a column of dates which were all before 1900, insisting that they were a column of text (since 1/1/1900 has numeric equivalent 1, and negative numbers are apparently not allowed). So I did a general replace of all my dates (which were in the 1800s) to put them into the 1900s, e.g. 180 -> 190, 181 -> 191, etc. The sorting process then worked fine. Finally I did a replace the other way, e.g. 190 -> 180.

Hope this helps some other historian out there.

It seems that Excel does not recognize your dates as dates, it recognizes them text, hence you get the Sort options as A to Z. If you do it correctly, you should get something like this:

Hence, it is important to ensure that Excel recognizes the date. The most simple way to do that, is use the shortcut CTRL+SHIFT+3.

Here's what I did to your data. I simply copied it from your post above and pasted it in excel. Then I applied the shortcut above, and I got the required sort option. Check the image.

I would suspect the issue is Excel not being able to understand the format... Although you select the Date format, it remains as Text.

You can test this easily: When you try to update the dates format, select the column and right click on it and choose format cells (as you already do) but choose the option 2001-03-14 (near bottom of the list). Then review the format of your cells.

I will suspect that only some of the date formats update correctly, indicating that Excel still treating it like a string, not a date (note the different formats in the below screen shot)!

There are work-arounds for this, but, none of them will be automated simply because you're exporting each time. I would suggest using VBa, where you can simply run a macro which converts from US to UK date format, but, this would mean copying and pasting the VBa into your sheet each time.

Alternatively, you create an Excel that reads the newly created exported Excel sheets (from exchange) and then execute the VBa to update the date format for you. I will assume the Exported Exchange Excel file will always have the same file name/directory and provide a working example:

So, create new Excel sheet, called ImportedData.xlsm (excel enabled). This is the file where we will import the Exported Exchange Excel file into!

Add this VBa to the ImportedData.xlsm file

Sub DoTheCopyBit()



Dim dateCol As String

dateCol = "A"        'UPDATE ME TO THE COLUMN OF THE DATE COLUMN



Dim directory As String, fileName As String, sheet As Worksheet, total As Integer



Application.ScreenUpdating = False

Application.DisplayAlerts = False



directory = "C:UsersDaveDesktop"                   'UPDATE ME

fileName = Dir(directory & "ExportedExcel.xlsx")       'UPDATE ME (this is the Exchange exported file location)



Do While fileName <> ""



'MAKE SURE THE EXPORTED FILE IS OPEN

Workbooks.Open (directory & fileName)



Workbooks(fileName).Worksheets("Sheet1").Copy _



Workbooks(fileName).Close



fileName = Dir()



Loop



Application.ScreenUpdating = True

Application.DisplayAlerts = True



Dim row As Integer

row = 1

Dim i As Integer

Do While (Range(dateCol & row).Value <> "")



     Dim splitty() As String

     splitty = Split(Range(dateCol & row).Value, "/")

     Range(dateCol & row).NumberFormat = "@"

     Range(dateCol & row).Value = splitty(2) + "/" + splitty(0) + "/" + splitty(1)

     Range(dateCol & row).NumberFormat = "yyyy-mm-dd"

     row = row + 1

Loop





End Sub

What I also did was update the date format to yyyy-mm-dd because this way, even if Excel gives you the sort filter A-Z instead of newest values, it still works!

I'm sure the above code has bugs in but I obviously have no idea what type of data you have but I tested it with the a single column of dates (as you have) and it works fine for me!

How do I add VBA in MS Office?

https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680

Simple solution here - create new column use =datevalue(cell) formula then copy the formula into your other rows- a few seconds to fix

I was dealing with a similar issue with thousands of rows extracted out of a SAP database and, inexplicably, two different date formats in the same date column (most being our standard "YYYY-MM-DD" but about 10% being "MM-DD-YYY"). Manually editing 650 rows once every month was not an option.

None (zero... 0... nil) of the options above worked. Yes, I tried them all. Copying to a text file or explicitly exporting to txt still, somehow, had no effect on the format (or lack therefo) of the 10% dates that simply sat in their corner refusing to behave.

The only way I was able to fix it was to insert a blank column to the right of the misbehaving date column and using the following, rather simplistic formula:

(assuming your misbehaving data is in Column D)

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))

I could then copy the results in my new, calculated column over top of the misbehaving dates by pasting "Values" only after which I could delete my calculated column.

I figured it out!

There is a space at the beginning and at the end of the date.

1. If you use find and replace and press the spacebar, it WILL NOT
   work.


2. You have to click right before the month number, press shift
   and the left arrow to select and copy. Sometimes you need to use the
   mouse to select the space.


3. Then paste this as the space in find and
   replace and all your dates will become dates.


4. If there is space and date Select Data>Go to Data>Text to
   columns>Delimited>Space as separator and then finish.


5. All spaces will be removed.

If Excel stubbornly refuses to recognize your column as date, replace it by another :

• Add a new column to the right of the old column


• Right-click the new column and select Format
  


• Set the format to date
  


• Highlight the entire old column and copy it


• Highlight the top cell of the new column and select Paste Special, and only paste values
  


• You can now remove the old column.

Had similar problem, tried formatting the data as date but to no avail. Then I realized there was a leading space in the date, edited the field and removed the space - hey presto everything ok. Not great for large amounts of data, but thinking about it further I could have maybe checked it and edited the data in Wordpad or a similar editor.

I just simply copied the number 1 (one) and multiplied it to the date column (data) using the PASTE SPECIAL function. It then changes to General formating i.e 42102
Then go apply Date on the formating and it now recognises it as a date.

Hope this helps

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.

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

03.01.17

I solved this with the following:

1. Highlight the whole column.


2. Go to find and select/ replace.


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


4. Keep the column highlighted.


5. Format cells, number tab select date.


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


7. Locale English (United Kingdom)

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

So I had the same issue. None of the solutions above either worked for me or I found them too complex for my skill level.

What I ended up doing was doing a text-to-columns on the date delimiter. I then used the date() function to recreate the date using the day, month and year cells. Worked.

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...

1. 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.




2. 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.




3. 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.




4. 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.

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) *

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

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

I found that CAST AS smalldatetime solves my problem. Solution found here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f0f8dd05-4212-428f-8099-748e87c637ae/format-data-from-sql-server-view-as-date-in-excel?forum=sqlkjpowerpivotforexcel

That of course only works, if you have access to the underlying query.

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.

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.

I was able to fix the problem after realizing that the "Show Formulas" was selected. in Excel 2016, go to Formulas, then the Formula Auditing section and make sure "Show Formulas" isn't selected. Fixed my problem.

I had the similar problem, but I noticed I had an extra spaces at the end of the date. I removed the spaces with replace command and now date sorting works fine.

this problem made me almost break my laptop

long story short, i had a similar problem where the dates are working just fine in some cells but keep flipping in others regardless if i copy paste or enter manually, i did the whole data text to column and cell formatting solutions and all of that didn't work.

the solution actually is not in excel, its in the region and language setting.. i know right !!!

to have the dates display as MM/DD/YYYY in the formats tab change the format to US

to have the dates display as DD/MM/YYYY in the formats tab change the format to UK

Voila !

I still had problem after following several answers. Than in the format, chose 14-Mar-01 and changed locale to English (United States).. And it worked perfectly. Hope this helps.

I use a mac.

Go to excel preferences> Edit> Date options> Automatically convert date system

Also,

Go to Tables & Filters> Group dates when filtering.

The problem probably started when you received a file with a different date system, and that screwed up your excel date function

I found a very easy answer. Dates were formatted 10.11.2018 and were not being recognised as dates. having first formatted the column as dates, I did a Ctrl-H search and replace of "." with "/". All dates were instantly recognised.

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 len(a1) just allows an extra zero to be added for months 1-9.