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

User · Answer

The quickest and easiest way to fix this is to do a find and replace on your date seperator  with the same separator  For example in this case Find  -  and Replace with  -   not sure why this works but you will find all dates are right-aligned as they should be after doing this

User · Answer

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

User · Answer

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

User · Answer

In your case it is probably taking them in DD-MM-YY format  not MM-DD-YY

User · Answer

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

User · Answer

The simplest solution is to put yy mm dd into the date   formula by first extracting them with left    mid   and right    In this case  assuming your input date is in A1    date right A1 2  100 left A1 2  mid A1 4 2     Explanation of above    right A1 2  gets the last two digits in the cell  yy   We add 100 because it defaults to 1911 instead 2011  omit  100 if it doesn t do that on yours    left A1 2  gets the first two digits in the cell  mm     mid A1 4 2  gets 2 digits in the middle of the cell  starting at 4th digit  dd    Why this happens in the first place   I come across this problem all the time when I import Canadian bank data into excel  In short  your input date format does not match your regional settings    Seems your setting mean Excel wants date input as either DD-MM-YY or YY-MM-DD  but your input data is formatted as MM-DD-YY   So  excel sees your days as months and vice-versa  which means any date with day below 12 will be recognized as a date  BUT THE WRONG DATE  month and day reversed  and any date with day above 12 won t be recognized as a date at all  because Excel sees the day as a 13th  month   Unfortunately  you can t just change the formatting  because Excel has already locked those day month assignments in place  and you just end up moving what Excel THINKS are days and months around visually  not reassigning them   Frankly  it is surprising to me there is not a date-reverse tool in excel  because I would think this happens all the time  But the formula above does it pretty simply   NOTE  if your dates don t have leading zeros  i e  4 8 11 vs 04 08 12  it gets trickier because you have to extract different amounts of digits depending on the date  i e  4 9 11 vs 4 10 11   You then have to build a couple if statements in your formula  Gross

User · Answer

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

User · Answer

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

User · Answer

It s 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    Select only the column of  dates   Click Data   Text to Columns Click Next  Click Next  In step 3 of the wizard  check  Date  under Col data format  then choose   DMY  from the droplist   Click Finish

User · Answer

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

[excel] Some dates recognized as dates, some dates not recognized. Why?

Examples related to excel

Examples related to date