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

Tags: excel date
By : J. Lee
Source: Stackoverflow.com
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!

By : J. Lee


Answers

Right-click on the column header and select Format Cells, the chose Date and select the desired date format. Those that are not recognized are ambiguous, and as such not interpreted as anything but that is resolved after applying formatting to the column. Note that for me, in Excel 2002 SP3, the dates given above are automatically and correctly interpreted as dates when pasting.

By : Benjamin


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

By : Frosty Z


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.

By : Greg


This video can help you solving your question :)
By: admin