Why does excel change cell format to date?

Question

I'm responsible for maintaining an excel file with multiple tabs. The file is linked to a data warehouse on one of our servers. At times when I open the file the cell formats automatically default to "date" instead of "general." This causes issues because when I go to refresh the file with updated information the "date" format is not recognized and the file won't refresh with the most recent data warehouse information. The odd thing is that the format doesn't change each time I open the file, but instead changes sporadically for no apparent reason. I have talked with multiple people, visited numerous websited and have been unable to find a cause (or solution) for this. Being able to prevent the format from going to "date" would eliminate many headaches!

Answer 1

Which problem?  There are two problems mentioned in this thread which have different solutions or work-arounds.

• Data input that looks like a date, but isn't, and MS converts it to a date.
  • Working around this depends on exactly what you are doing to get the data into Excel.  It may be as simple as doing an Import rather than an Open on a text or csv file
  • However, it can be much more complex, and has been the subject of many complaints
• All the numbers on a worksheet randomly changing to a date.
  • This was the problem in the particular message to which you replied.
  • This is less common and was first reported in Excel 2007.
    • For reasons which I have never seen explained, the "Normal" cell style format gets corrupted.
    • To fix this, Home ► Styles ► Cell Styles
    • In the upper left corner of the "Good, Bad Neutral" dialog box, right click on the bar that reads "Normal" and be sure the Number format is set to General

Answer 2

All I see here are multiple reports of the problem.  How about a solution?

Answer 3

I am having the same problem with multiple worksheets that I have to maintain at work.  One column is simply a text field where I write up a paragraph and occasionally the format is automatically changed to "date."  The other is a pivot table that is supposed to draw in part numbers and then by serial number.  The serial numbers constantly change to "date" format no matter how many times I change them back (despite my continued confirmation that the format is correct in the source data the pivot table is drawing from).

Answer 4

Can you provide some data samples of what is in that column? I'm guessing that if you paste (or retrieve) values that could be interpreted as a date, Excel if probably just trying to be helpful.

If you have data like 832-542-12 then I wouldn't expect Excel to be 'helpful' but if you have 12-05-11 then that could be considered a date, and Excel might convert your data for you.

HTH,

Keith

Answer 5

I'm glad you've called this issue out, since I'm having the same problem at work.  The numbers do not have to have any hyphens or slashes for excel to do this, and it is sporadic.  When it happens, the entire sheet changes to a date format, with the exception of cells that I had formatted other than general, which was the default for the entire sheet as I created the spreadsheet.