snopes.com Post new topic  Post a reply
search | faq | forum home

  next oldest topic   next newest topic
» Hello snopes.com » Non-UL Chat » Techno-Babble » Evil Excel auto formatting

 - UBBFriend: Email this page to someone!    
Author Topic: Evil Excel auto formatting
Hans Off
Markdown, the Herald Angels Sing


Icon 1 posted      Profile for Hans Off   E-mail Hans Off   Send new private message       Edit/Delete post   Reply with quote 
Before I throw it out of the window...

How do I stop Excel2003 attempting to reformat anything that vaguely looks like a date into date format?


The bloody think is naffing up my sort codes!

--------------------
"British English speakers point to Americans adding more syllables so that they can make even more noise without actually saying anything." Llewtrah


Posts: 2235 | From: Sussex , UK | Registered: May 2004  |  IP: Logged | Report this post to a moderator
Blue Fuzzy Thing
I'll Be Home for After Christmas Sales


Icon 1 posted      Profile for Blue Fuzzy Thing   E-mail Blue Fuzzy Thing   Send new private message       Edit/Delete post   Reply with quote 
I haven't used Excel 2003, but I would suggest trying to change the format of the cell/column to Text. That works in Excel 2000.

Blue Fuzzy Thing

--------------------
People say I have ADD, but they just don’t understand that... Oh look! A chicken!

Posts: 151 | From: Cincinnati, Ohio | Registered: Nov 2005  |  IP: Logged | Report this post to a moderator
Seaboe Muffinchucker
Let There Be PCs on Earth


Icon 206 posted      Profile for Seaboe Muffinchucker     Send new private message       Edit/Delete post   Reply with quote 
quote:
Originally posted by Hans Off:
How do I stop Excel2003 attempting to reformat anything that vaguely looks like a date into date format?

Grab the entire column and tell it to either format as general number (if they're all numbers) or, as Blue Fuzzy says, as text. That should stop it.

Seaboe

EB they (plural) might all be numbers, not their (possessive) all numbers.

--------------------
Education is not the filling of a hard drive, but the lighting of a bulb. -- Yeats via Esprise Me

Posts: 5562 | From: Seattle, WA | Registered: Jun 2005  |  IP: Logged | Report this post to a moderator
InfraPurple
The First USA Noel


Icon 1 posted      Profile for InfraPurple     Send new private message       Edit/Delete post   Reply with quote 
Can you still force a label? I seem to recall that if you put an apostrophe in front of anything it considers it a label and puts it in the cell as straight text.

--------------------
While I wasn't falling down or anything, gravity and I did have an interesting relationship for a short time. - Purple Iguana

Posts: 715 | From: Kansas | Registered: Feb 2005  |  IP: Logged | Report this post to a moderator
Hans Off
Markdown, the Herald Angels Sing


Icon 1 posted      Profile for Hans Off   E-mail Hans Off   Send new private message       Edit/Delete post   Reply with quote 
Changing the format back just ****s the original data up.

I think it is an auto setting on one of my colleague's machines

It's part of the "error checking" module that has been added to excel I want to disable it permanently!

hatehatehate

--------------------
"British English speakers point to Americans adding more syllables so that they can make even more noise without actually saying anything." Llewtrah


Posts: 2235 | From: Sussex , UK | Registered: May 2004  |  IP: Logged | Report this post to a moderator
Seaboe Muffinchucker
Let There Be PCs on Earth


Icon 206 posted      Profile for Seaboe Muffinchucker     Send new private message       Edit/Delete post   Reply with quote 
quote:
Originally posted by Hans Off:
It's part of the "error checking" module that has been added to excel I want to disable it permanently!

Hans, I think you can. Or you can come close, anyway. Go to Tools -> Options -> Error Checking and uncheck all the boxes. If there are no rules to be applied there should be no error checking.

Seaboe

--------------------
Education is not the filling of a hard drive, but the lighting of a bulb. -- Yeats via Esprise Me

Posts: 5562 | From: Seattle, WA | Registered: Jun 2005  |  IP: Logged | Report this post to a moderator
Atlanta Jake
Xboxing Day


Icon 1 posted      Profile for Atlanta Jake   E-mail Atlanta Jake   Send new private message       Edit/Delete post   Reply with quote 
If you still want the date to appear in some of the cells, I'd go with the label fix...

Just type an apostrophe in front of whatever you want to appear unchanged.

If you want it to still treat it as a number, go with seaboes' solution, or highlight the affected cells. right click and format those cells... should work.

If not, you have something that I've never encountered... and I use Excel (many different versions) quite regularly.

--------------------
Remember Kids, Don't try this at home!

Posts: 1366 | From: Atlanta, Georgia | Registered: Sep 2001  |  IP: Logged | Report this post to a moderator
Hans Off
Markdown, the Herald Angels Sing


Icon 1 posted      Profile for Hans Off   E-mail Hans Off   Send new private message       Edit/Delete post   Reply with quote 
It's new to Excel 2003. I know Excel has always "auto formatted" to an extent but in the past you were (IIRC) always able to "undo" it!


Here is an example of what I mean...

Open a new spreadsheet..

1. Type 09-01-26 in any cell (which is the sort code for Abbey National)

2. click out of the cell or press enter...

3. "Helpful" excel changes the format to 09/01/1926

4. Frown, mutter "that isn't supposed to be a date excel"

5. go to the undo facility to discover that an auto formatting entry isn't recorded

6. right click on the cell and change the cell formatting from 'date' to 'general' or 'text'

7. scream as your original 09-01-26 entry now reads 46031 thereby buggering up records required to be kept by the FSA.


Please make it stop!

Excel is changing my data BY DEFAULT!!!! I mean WTF? I wouldn't mind a paperclip saying "gee this looks like a date" but to just change the raw data? ARRRRRRRRRRRRRRRRRRRGHJHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH

--------------------
"British English speakers point to Americans adding more syllables so that they can make even more noise without actually saying anything." Llewtrah


Posts: 2235 | From: Sussex , UK | Registered: May 2004  |  IP: Logged | Report this post to a moderator
Green Kangaroo
The First USA Noel


Icon 1 posted      Profile for Green Kangaroo   E-mail Green Kangaroo   Send new private message       Edit/Delete post   Reply with quote 
If you put a single apostrophe in front of your number (i.e., '09-01-26) it's like telling excel to leave it alone (or at least, that's what I use it to do). The apostrophe does not show up anywhere but in the formula bar.

--------------------
I don't know if I'd survive without a friend like you in my life. Brett Dennen

Posts: 637 | From: Arlington, VA | Registered: Oct 2002  |  IP: Logged | Report this post to a moderator
Hans Off
Markdown, the Herald Angels Sing


Icon 1 posted      Profile for Hans Off   E-mail Hans Off   Send new private message       Edit/Delete post   Reply with quote 
No good. I (and my colleagues) are copying and pasting large online reports into a spreadsheet.

I just don't want Excel to change anything.

--------------------
"British English speakers point to Americans adding more syllables so that they can make even more noise without actually saying anything." Llewtrah


Posts: 2235 | From: Sussex , UK | Registered: May 2004  |  IP: Logged | Report this post to a moderator
Enjal
Little Sales Drummer Boy


Icon 1 posted      Profile for Enjal   E-mail Enjal   Send new private message       Edit/Delete post   Reply with quote 
Hans, I was going to say to use "Paste Special" but I tested it out myself and it doesn't work. I even tried going through the help search but of course there's no good answer there. I think all you can do at this point is maybe contact Microsoft Office support? There should be a link on your help menu that says "contact us".

Good luck!

--------------------
"I'm a leaf in the wind"
New Lungs for George

Posts: 2719 | From: the other Portland (US) | Registered: Apr 2001  |  IP: Logged | Report this post to a moderator
HollowMan
Deck the Malls


Icon 1 posted      Profile for HollowMan     Send new private message       Edit/Delete post   Reply with quote 
quote:
Originally posted by Hans Off:
No good. I (and my colleagues) are copying and pasting large online reports into a spreadsheet.

I just don't want Excel to change anything.

If you want your information to appear exactly like the document you're copying from, select the whole sheet and format as text. Then, Paste Special as text. Can't help about recovering previous stuff though.

--------------------
Heisenberg may have slept here.

I got an idea... an idea so smart my head would explode if I even began to know what I was talking about.

Posts: 291 | From: Greenville, SC | Registered: Apr 2005  |  IP: Logged | Report this post to a moderator
Hans Off
Markdown, the Herald Angels Sing


Icon 1 posted      Profile for Hans Off   E-mail Hans Off   Send new private message       Edit/Delete post   Reply with quote 
It's a workaround, but I am still flabbergasted that Excel behaves in such a brattish way!

--------------------
"British English speakers point to Americans adding more syllables so that they can make even more noise without actually saying anything." Llewtrah


Posts: 2235 | From: Sussex , UK | Registered: May 2004  |  IP: Logged | Report this post to a moderator
Griffin at the Maul
Joyeux New Sale


Icon 1 posted      Profile for Griffin at the Maul     Send new private message       Edit/Delete post   Reply with quote 
Hans, would you REALLY expect anything but brattiness from a M$ product? They think that things are only done their way, and make it a total pain in the butt to do it any other way.

--------------------
Where are we going, and why are we in this handbasket?

Posts: 782 | From: Arlington, TX | Registered: Jul 2005  |  IP: Logged | Report this post to a moderator
unbroken
We Wish You a Merry Giftmas


Icon 1 posted      Profile for unbroken   Author's Homepage     Send new private message       Edit/Delete post   Reply with quote 
OpenOffice does exactly the same thing, and oddly enough, MS Works Spreadsheet doesn't. (I found this out when trying to figure out what the connection was between 09-01-26 and 46031. I still haven't figured that one out, but I have learned that such tomfoolery is not the sole preserve of Microsoft.)

--------------------
Oddly enough, the island of Ireland looks remarkably like a small old man driving an old Ford Fiesta.

Posts: 950 | From: Dublin | Registered: Apr 2003  |  IP: Logged | Report this post to a moderator
Eddylizard
We Wish You a Merry Giftmas


Icon 1 posted      Profile for Eddylizard     Send new private message       Edit/Delete post   Reply with quote 
quote:
Originally posted by unbroken:
(I found this out when trying to figure out what the connection was between 09-01-26 and 46031. I still haven't figured that one out, but I have learned that such tomfoolery is not the sole preserve of Microsoft.)

Excel stores dates as a number, which is the number of days between a start date (I think it's January 1st 1900) and the date you enter. So January 3rd 1900 would be stored as 3. Then if the cell is formatted as a date field, it converts that number back to a date. This makes it easy to subtract or add dates (Whats the 3rd of September 2001 plus 96 days?) If you reformat the cell to a number format, the date will be displayed as a number.

--------------------
"Ladies and gentlemen, this is what is commonly known as money. It comes in all sizes, colours, and denominations - like people."

Posts: 997 | From: Maidstone, UK | Registered: Jun 2006  |  IP: Logged | Report this post to a moderator
unbroken
We Wish You a Merry Giftmas


Icon 1 posted      Profile for unbroken   Author's Homepage     Send new private message       Edit/Delete post   Reply with quote 
Ooo. Thanks! I don't think I'd ever have figured that out on my own.

--------------------
Oddly enough, the island of Ireland looks remarkably like a small old man driving an old Ford Fiesta.

Posts: 950 | From: Dublin | Registered: Apr 2003  |  IP: Logged | Report this post to a moderator
Hans Off
Markdown, the Herald Angels Sing


Icon 1 posted      Profile for Hans Off   E-mail Hans Off   Send new private message       Edit/Delete post   Reply with quote 
If i ever become really really bored i would write a conversion formula to fix the problems along the lines of....

(assuming 09/01/26 is in cell A1)


set column B to text enter something like =CONCATENATE(LEFT(A1,2),"-"...etcetc ...,RIGHT(A1,2))

and throw in some format conversiont thing..

But I am too busy to be that bored!

--------------------
"British English speakers point to Americans adding more syllables so that they can make even more noise without actually saying anything." Llewtrah


Posts: 2235 | From: Sussex , UK | Registered: May 2004  |  IP: Logged | Report this post to a moderator
   

Quick Reply
Message:

HTML is enabled.
UBB Code™ is enabled.

Instant Graemlins
   


Post new topic  Post a reply Close topic   Feature Topic   Move Topic   Delete topic next oldest topic   next newest topic
 - Printer-friendly view of this topic
Hop To:


Urban Legends Reference Pages

Powered by Infopop Corporation
UBB.classic™ 6.7.2