Stop auto correction of number into a date

I didn't mean to seem condescending.  Sorry it came off that way.  But clearly, a specification that has been present for years, and been complained about for years, is not going to change any time soon.  So at this point in time, it seems your only choices are to develop a work-around, or use a different tool.  I have read that Open Office does not have this same "feature", but I have not tried it myself.

This has been complained about here and elsewhere for as long as I can recall.  And MS has not deigned to change the behavior.  Most posters here have no connection  with MS, other than as users of their products, and a desire to help others.

If you read my suggestion closely, you will note that I did not suggest the workaround that you seem to be applying of pre-formatting a sheet as text and then copy/pasting and then using the Text-to-Columns feature of the Data tab.  Rather I suggested using the Data Import (also on the Data tab, but in a different spot) feature which allows you to format only selected columns as text prior to importing a text or CSV file.  (Get External Data  From Text)

Please try that method and see if it works for you.  If it does, perhaps you can adjust your vlookup formula to search for text strings in the "date-lookalike columns" and numeric values in the other columns.  Or provide some data samples and environment descriptions of where you are having the problem so perhaps a less cumbersome workaround can be suggested.

EDIT:  I just checked my copy of OO and it does not seem to have an option to disable that "feature".   Doing a web search for OO likewise reveals the same complaints.  There is one difference in that, with OO, copy/pasting text (at least from a Notepad file), brings up the Text to Columns wizard BEFORE pasting, so that differential columnar formatting can be applied at that point.  In order to do that in Excel, you need to use the Data Import from Text file feature.   (Get External Data  From Text)

it is not Norwegian Excel date format.  In 2003 you could work around this for all sheets and especially if you created a master sheet, so it always uploaded your features and not the MS defaults.

 

Unfortuantely they screwed with this feature in 2007 and maintained the @#@%#@ 2010 and will probably defend themselves. 

 

Interesting.  I've never used a worksheet or workbook template so have no experience there.  I have read that Open Office treats date data differently but I've never used it.  Perhaps it is something that might work better for you.

EDIT:  I just checked my copy of OO and it does not seem to have an option to disable that "feature".   Doing a web search for OO likewise reveals the same complaints.  There is one difference in that, with OO, copy/pasting text (at least from a Notepad file), brings up the Text to Columns wizard BEFORE pasting, so that differential columnar formatting can be applied at that point.  In order to do that in Excel, you need to use the Data Import from Text file feature.   (Get External Data  From Text)

something happended with 2010...

 the workbook template seems work.   Just put it in the excel-office start directory and you are set with new work books.

however, the work sheet template does not work like it did with 2003 and 2007.  Although it lets you set up a worksheet template as a physical file.  Excel 2010 does not use it when it insert an new sheet it into an open workbook.   So in 2010 it is better to set up a single click macro on your Developer Tool that inserts a worksheet and then changes all the worksheet defaults to something more useful for you--such as a specific number or text formats in every cell---and in that way you avoid the date conversion when you use the "new" worksheet.