how to turn off excel's auto formatting of data.

[spreadthelove]

hello,

i often have the most annoying problem with excel. it automatically
decides how to format specific data.

here's one example. i've imported a text file with some timing information in minutes:seconds. every time excel automatically makes it
hours:minutes. if i go and format the information to minutes:seconds,
the data is then show as incorrect.

here's another example. the data is 28:17 (28 minutes, 17 seconds). well, when i import it, the data is now conveted to "01-01-1900 04:17:00". even when i try to copy the format of another cell, it then changes the data to 04:17:00. argh!!! why on earth does excel change 28:17 to a date and a time in 1900?

another classic one is if you type 05-09, excel will change it to 05-sep. as if the information is a date in -- 05 september 2003. it's so irritating that it does this.

what causes this to happen? why does it automatically decide how to
format the information? whatever it is i would like to turn it off so
that excel never formats any data until i tell it to.

any help would be appreciated.
bruno

 

[Cadmister]

I have also seen the suggestion copy the section to notepad and import as text from excel. I would just think there has to be a much easier way.

Frustration from “smart” software does not really benefit in “getting the job done”. It is so good to know that Microsoft or Mr. Gates if you please, already knew what I wanted in my spreadsheet before I got to work this morning. Not. Perhaps there will be those using the “New & improved” version of excel that could be influential in directly requesting of the “creators” that we humble users that really do not know what is good for us, be allowed to turn off the blasted auto formatting feature. I’m really new at using spreadsheets since I just started using spreads when Lotus123 was the popular choice. Could have been that it (Lotus123)was so user friendly with simple macros that the end user could create to do all kinds of tasks that were unique to the users needs. Oh, I seem to have forgotten that that has all been done for us now and all we have to do is put on or dunces caps and continue. Mr. Gates “GIVE US A MEANS TO TURN OFF THAT AUTO FORMATTING”. Thanks

 

[Tonda]

There's no way, and several methods have been suggested to live with it.

I just ruined my project a would have to redo it, because excel in his all-to-date converting deleted some numbers here and there. I KNOW he does his stupid conversion. I know how to go around it. I just forgot. You know how I feel about software, which punishes me for forgetting HIS OWN stupid behaviour by ruining my whole day work? If the auto formatting can not be turn off, I have to turn off excel and start using something else. I am not happy about it, excel has many useful features, but obviously there is no better way.

 

[syandras]

I dont know who the hell is PaddyD, why he thinks if he doesnt know the answer of a question there no answer at all, but where he is right, that Excel wont help you. The "solution" bellow is just a hack on your computer, its not an overall answer.

Its not the Excel. Windows does recognize the data as a date and autocorrects. You have to change the Windows settings.
"Control Panel" (-> "Switch to Classic View") -> "Regional and Language Options" -> tab "Regional Options" -> "Customize..." -> tab "Numbers" -> And then change the symbols according to what you want.
enable/disable auto-convert a NUMBER to DATE
It will work on your computer, if these settings are not changed for example on your customers' computer they will see dates instead of data. But you can work and tell the "solution".

 

[stu40]

The MS Excel development team are holding a feedback forum of suggestions for the next version of Excel. There is a submission to turn off auto date formatting here - Allow us to turn off automatic conversion to date. Please vote it up as a way of solving this issue.