Unable to get Excel to recognise date in column

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.

Select all the column and go to Locate and Replace and just replace "/" with /.

I was dealing with a similar issue with thousands of rows extracted out of a SAP database and, inexplicably, two different date formats in the same date column (most being our standard "YYYY-MM-DD" but about 10% being "MM-DD-YYY"). Manually editing 650 rows once every month was not an option.

None (zero... 0... nil) of the options above worked. Yes, I tried them all. Copying to a text file or explicitly exporting to txt still, somehow, had no effect on the format (or lack therefo) of the 10% dates that simply sat in their corner refusing to behave.

The only way I was able to fix it was to insert a blank column to the right of the misbehaving date column and using the following, rather simplistic formula:

(assuming your misbehaving data is in Column D)

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))

I could then copy the results in my new, calculated column over top of the misbehaving dates by pasting "Values" only after which I could delete my calculated column.