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/2011
In Excel, I have highlighted the column and selected
Format Cells.... In this dialog box, I have selected the
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.