Some dates recognized as dates, some dates not recognized. Why?

Question

Here is a list of dates:

04-22-11
12-19-11
11-04-11
12-08-11
09-27-11
09-27-11
04-01-11

When you copy this list in Excel, some of them are recognized as dates, others not, in the following manner:

04-22-11
12-19-11
11-04-11 (date)
12-08-11 (date)
09-27-11
09-27-11
04-01-11 (date)

Does anyone know why? And how to force Excel to recognize all list items as dates?

Many thanks!

Answer 1

It is caused by Excel auto-recognizing/formatting the cell contents, but in unclear/inconsistent ways. Fixing it is not that hard...

Check out this forum post:

http://www.pcreview.co.uk/forums/excel-not-recognizing-dates-dates-t3139469.html

The steps in short:

1. Select only the column of "dates"
2. Click Data > Text to Columns
3. Click Next
4. Click Next
5. In step 3 of the wizard, check "Date" under Col data format, then choose: "DMY" from the droplist.
6. Click Finish

Answer 2

This is caused by the regional settings of your computer.

When you paste data into excel it is only a bunch of strings (not dates).

Excel has some logic in it to recognize your current data formats as well as a few similar date formats or obvious date formats where it can assume it is a date. When it is able to match your pasted in data to a valid date then it will format it as a date in the cell it is in.

Your specific example is due to your list of dates is formatted as "m/d/yy" which is US format. it pastes correctly in my excel because I have my regional setting set to "US English" (even though I'm Canadian :) )

If you system is set to Canadian English/French format then it will expect "d/m/yy" format and not recognize any date where the month is > 13.

The best way to import data, that contains dates, into excel is to copy it in this format.

2011-04-22 2011-12-19 2011-11-04 2011-12-08 2011-09-27 2011-09-27 2011-04-01 

Which is "yyyy-MM-dd", this format is recognized the same way on every computer I have ever seen (is often refered to as ODBC format or Standard format) where the units are always from greatest to least weight ("yyyy-MM-dd HH:mm:ss.fff") another side effect is it will sort correctly as a string.

To avoid swaping your regional settings back and forth you may consider writting a macro in excel to paste the data in. a simple popup format and some basic logic to reformat the dates would not be too difficult.