Excel Date Format - users should be able to override it automatic.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am an accountant that uses Excel extensively in my work. On a very regular
basis, I enter account numbers, often in formats that Excel is programmed to
recognize as dates, and as a result it frequently (and incorrectly) reformats
the data I enter as a date. Now, when I'm posting hundreds of account
numbers from the clipboard, it is very time-consuming, not to mention
frustrating, to have to go back and manually correct each one.

Obviously, I can't just highlight the affected cells and change the format
back, since the date format actually changes the data in the cell. I've also
tried "pre-formatting" the page as well, but have had little success.

I've searched extensively through Excel's menus, and have found no way to
disable or override this autoformat feature. If there is a way to do this in
Excel 2003, I'd appreciate hearing about it. If there isn't, I think it
would be a very helpful feature on a future version of Excel. I find it odd
that a program as powerful as Excel would have such a frustrating quirk.
 
There is no built in way to turn it off, I use either preformatting as text
or
precede each entry with an apostrophe '
it will not be visible in the cell
 
Thanks for the info. When entering manually, I use the apostrophe. I can't
use apostrophes when I export a block of info from another program (such as
QuickBooks) and paste it into Excel. I'll have to try preformatting again.
 
Although this might be overkill, you can save it to a text file first, then
when you open it in excel
the text import wizard will pop up, then you can click next twice and
finally under column data format select text
and it will import as you expect it
 
Now that's a new idea to me... I just tried it, and it worked - tedious for
sure, but hey, it's a lot faster than the way I used to do it. That helps a
lot. Thanks!!
 
Hi

When entering data manually, format the column with account numbers as text
before it.
When copy-paste'ing data, format the column as text before, and then use
PasteSpecial Values.

Arvi Laanemets
 
You have two (actually three) options:
1. Format the cell(s) as Text before data is entered.
2. Precede your entry with an apostrophe (I know you said not to mention
this, but you are incorrect that it "doesn't convert")
3. Don't use Excel, if it's causing you so much stress.

Regards,
Fred
 
Back
Top