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