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

 

[tlspam]

Hi - welcome to the board!

Why not just format the cells in question as you want them before you construct the formulas / inport the data?

@PaddyD: Because you SHOULDN'T HAVE TO.

This is nothing but an annoyance that Microsoft calls a "feature" because they THINK in their asinine (lack of) wisdom that if someone types in "5-9" they are referring to the date May 9. Effin stupid and useless if you ask me.

The whole point of "General" formatting is that whatever is entered in a cell should be interpreted the way IT IS ENTERED without "intuitive" (AKA B.S.) analysis applied to it and changed from what is literally entered.

This amounts to nothing but computers and technology GETTING IN THE WAY of human productivity. The real Microsoft answer should be "if you want something in a single cell or series of cells formatted as a date, you need to tell Excel (AKA format the cells in question) as a date and not permit the software to imply anything other than unformated data strings as entered by a user.

Or in other words, software like Excel needs to assume that people actually have a brain of their own and if they want something such as a date format applied to something then leave it up to the user to format it that way; instead forcing the user to interrupt what they are doing, stop and format a cell as "text" then having to RE-enter the same data a second time so you get what you wanted 5 minutes ago before all this "intuitive" B.S. intervened and interrupted me because the software is programmed to think "Oh, this is what he means when he enters something like this" AKA the software jumping to its own flawed conclusions and assuming everyone using it is a moron and interpreting "5-9" as a date just because you happen to enter "5-9" into a cell.

 

[shg]

Do you feel strongly about this? How long have you felt this way?

 

[NickHerllick]

Hi there - new subscriber. I run into this issue every week, working with clients of our database app for fund raising. They are constantly using import tools that read csv files downloaded from 3rd party sources. And prior to import they sometimes open the files in Excel, e.g. to correct spelling errors or to add a column, etc. Or they just want to view the data but they save it when asked, although they made no changes other than widenning some columns for viewing purposes. If they are not absolutely eagle-eyed, something unexpected usually happens to numeric or date data. And that something is almost always not what was intended or is needed for a successful import into the db.

While the earlier answers in this thread provide useful work-arounds offered with the best of intentions, they answer the question "how to live with Excel's auto formatting of data." not the question that posed by the Title.

I agree with the last posting I saw from PADDYD. There needs to be an OFF option in Excel's config. If it's there I haven't found it. It's been needed for as long as Excel has insisted on "helping" with auto-formatting. It's just good software etiquette to allow the user to choose a feature or to ignore it. And then ship the product with that feature off, but clearly show in docs how to turn it on. That's how we handle new features in our app - first do no harm, by slamming the feature into active status - just because a client was updated for a different reason, but the new build has three other new features.

We work hard with our clients' 3rd party data providers so they return data in a specific format compatible with our app. And it's way too easy to have that work ruined by Excel's auto-formatting. We actually had to add our own "CSV File Viewer" utility in our app so we could tell clients to use that instead of Excel, to safely inspect data prior to import.

And so, eleven messages into this thread, I repeat the original question...

how to turn off Excel's auto formatting of data(?)

So far I'm hearing there's NO WAY, here's how top live with it.

N Herlick

 

[shg]

Welcome to the board.

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

 

[VAD]

This issue has bugged me so much, I finally decided register and chime in with people like NickHerllick and tlspam.

One thing I didn't see mentioned yet, is that even if you change the darn format back to "General", a day or so later, XL decides to reformat to date format!!!! I don't know how many times I've changed it back... obnoxious! I believe MS Word has ways of shutting off some of its functions like 'auto correct'... I'd love to believe the programers for Excel did the same!

I'm not sure if the fact that I'm storing my tracker spreadsheet on a network drive has anything to do with it... This affects thousands of cells for me across several tabs...

Very frustrating!!!

I'll check back here every so often to see if someone figures out how to shut off that auto formatting. And, if I miraculously find the solution, I will definitely share with this forum!

 

[bluewolf]

My problem is I will try and format a colum or row to the data I wish but Excel 2007 will igonore and convert my data to $!!!

Glad I didn't buy this piece of junk!

 

[RSmith823]

I am constantly doing an auto import from a program to excel and it is converting my item numbers to date. I tried Millsy's VBS function but can't make it work. I just copied and pasted any help fixing this would be greatly appreciated.

 

[shg]

If you change the file extension to txt, you can import using the text import wizard and control the formatting of each field.

 

[RSmith823]

Unfortunatly the program I use doesn't offer a way to change the format. You simply Click the export to Excel button and it immediatly opens an Excel Spreadsheet with the information in it.

 

[RSmith823]

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.