Unable to get Excel to recognise date in column?

Question

I constantly have problems working with dates in Excel, I must be doing something wrong but I don't understand what.I have a spreadsheet, exported from our exchange server, that contains a column with dates on. They have come out in US format even though I'm in the UK. The column in question looks like this 04/08/2012 04/09/2009 04/01/2010 04/21/2011 04/05/2012 08/30/2009 08/29/2010 08/28/2011In Excel, I have highlighted the column and selected `Format Cells...`. In this dialog box, I have selected the `Date`, selected `English (United States)` as the locale and chosen the matching date format from the list. I hit OK and try to sort the data by this column. In the sort dialogue I choose this column, select sort on Values but the order only gives me options for A to Z, not oldest to newest as I would expect.This in turn sorts the date data by the first two digits.I am aware I could re-format this data to ISO and then the A to Z sort would work but I shouldn't have too, I'm obviously missing something. What is it?**EDIT:** I messed up the bounty but this should have gone to *@r0berts* answer, his first suggestion of Text to Columns with no delimiter and choosing 'MDY' as the type of data works. Additionally, if you have a time (i.e. `04/21/2015 18:34:22`), you need to first get rid of the time data. However after that the method suggested by *@r0berts* works fine.

Answer 1

Indeed many times when importing a data set from SQL or some other ERP system, the underlying data will not be recognized as a Date even though your cell is formatted as a Date; it is many times a text (which I believe is a smaller bit size than date - confirmation pls on this!). The only thing to do is to transform the underlying data to a Date Type by doing exactly as Robert suggested above, or you can alternatively create a query and pull your data in to Excel that way. You will still need to change the Data Type to Date in your query. Excel Query will be able to handle Date/Time.

Hope that helps!