Unable to get Excel to recognise date in column
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.
Solution 1:
The problem: Excel does not want to recognize dates as dates, even though through "Format cells - Number - Custom" you are explicitly trying to tell it these are dates by "mm/dd/yyyy". As you know; when excel has recognized something as a date, it further stores this as a number - such as "41004" but displays as date according to format you specify. To add to confusion excel may convert only part of your dates such as 08/04/2009, but leave other e.g. 07/28/2009 unconverted.
Solution: steps 1 and then 2
1) Select the date column. Under Data choose button Text to Columns. On first screen leave radio button on "delimited" and click Next. Unclick any of the delimiter boxes (any boxes blank; no checkmarks) and click Next. Under column data format choose Date and select MDY in the adjacent combo box and click Finish. Now you got date values (i.e. Excel has recognised your values as Date data type), but the formatting is likely still the locale date, not the mm/dd/yyyy you want.
2) To get the desired US date format displayed properly you first need to select the column (if unselected) then under Cell Format - Number choose Date AND select Locale : English (US). This will give you format like "m/d/yy". Then you can select Custom and there you can either type "mm/dd/yyyy" or choose this from the list of custom strings.
Alternative 0 : use LibreOffice Calc. Upon pasting data from Patrick's post choose Paste Special (Ctrl+Shift+V) and choose Unformatted Text. This will open "Import Text" dialog box. Character set remains Unicode but for language choose English(USA); you should also check the box "Detect special numbers". Your dates immediately appear in the default US format and are date-sortable. If you wish the special US format MM/DD/YYYY you need to specify this once through "format Cells" - either before or after pasting.
One might say - Excel should have recognised dates as soon as I told it via "Cell Format" and I couldn't agree more. Unfortunately it is only through step 1 from above that I have been able to make Excel recognize these text strings as dates. Obviously if you do this a lot it is pain in the neck and you might put together a visual basic routine that would do this for you at a push of a button. It can be as simple as this VBA code in Excel:
Sub RemoveApostrophe()
For Each CurrentCell In Selection
If CurrentCell.HasFormula = False Then
CurrentCell.Formula = CurrentCell.Value
End If
Next
End Sub
Alternative 1: Data | Text to Columns
Update on leading apostrophe after pasting: You can see in the formula bar that in the cell where date was not recognised there is a leading apostrophe. That means in the cell formatted as a number (or a date) there is a text string that program thinks - you want to preserve as a text string. You could say - the leading apostrophe prevents the spreadsheet to recognise the number. You need to know to look in formula bar for this - because the spreadsheet simply displays what looks like a left-aligned number. To deal with this, select the column you want to correct, choose in menu Data | Text to Columns and click OK. Sometimes you will be able to specify the data type, but if you have previously set the format of the column to be your particular data type - you will not need it. The command is really meant to split a text column in two or more using a delimiter, but it works like a charm for this problem too. I have tested it in Libreoffice, but there is the same menu item in Excel too.
Alternative 2: Edit Replace in Libreoffice
This is the quickest and best way so far, but that does not work in MsOffice as far as I know. Libreoffice Calc has option to search/replace using Regexps (aka. Regular Expressions) - what you do is find the cell and replace with itself and in the process Calc re-recognises the number as number and gets rid of the leading apostrophe. It works extremely quick. Select your column. Ctrl-H opens find-replace dialogue. Check 'Current selection' and 'Use regular expressions'. In the find box enter ^[0-9] - which means 'find any cell that has a digit 0 to 9 in its first position'. In the replace box enter & - which for libreoffice means 'for replacement use string you found in the search box'. Click Replace All - and your values are recognised for numbers that they are. The beauty is - it works on cells that contain only numbers with a leading apostrophe, nothing else - i.e. it will not touch cells that contain apostrophe-a space (or two)-then number, or cells that contain capital O instead of zero or any other anomalies you want to correct by hand.
Solution 2:
Select all the column and go to Locate and Replace and just replace "/" with /.