Some dates recognized as dates, some dates not rec

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

Here is what worked for me. I highlighted the column with all my dates. Under the Data tab, I selected 'text to columns' and selected the 'Delimited' box, I hit next and finish. Although it didn't seem like anything changed, Excel now read the column as dates and I was able to sort by dates.

Answer 2

Here is what worked for me on a mm/dd/yyyy format:

=DATE(VALUE(RIGHT(A1,4)),VALUE(LEFT(A1,2)),VALUE(MID(A1,4,2)))

Convert the cell with the formula to date format and drag the formula down.

Answer 3

A workaround for this problem consists in temporarily changing your regional settings, so the date format of the CSV imported file "matches" the regional settings one.

Open Office seems to work in a similar way for that issue, see: http://www.oooforum.org/forum/viewtopic.phtml?t=85898

Answer 4

I come across this problem when I tried to convert to Australian date format in excel. I split the cell with delimiter and used the following code from split cells then altered the issue areas.

=date(dd,mm,yy)