46
10
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.
I would suspect the issue is Excel not knowing how to do this - you can test. 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). I will suspect that only some of the date formats update correctly, indicating that Excel still treating it like a string, not a date! – Dave – 2015-03-17T09:14:14.277
What is the format of the exported data? Is it CSV or XLSX? – Excellll – 2015-03-18T14:44:24.607