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

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


--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

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.

"Peo Sjoblom" wrote:

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


--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

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

--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

"Peo Sjoblom" wrote:

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


--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

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!!

"Peo Sjoblom" wrote:

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

--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

"Peo Sjoblom" wrote:

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


--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

Excel Date Format - users should be able to override it automa
 

I work for a large pharmaceutical company and like many of you we have loads
of data.

This is indeed the dumbest function ever invented. And there's no way of
turning off/over riding it.

I've spend an entire hour on the web, only to find out from this forum that
Microsoft has not invented a way to turn this useless function off!!

When the data in the cell is '10-48' why can't it remain as that? and not
converted to October 1948?

Let me turn it off damn it!

and don't tell me to put a ^%#($*&^#@$ apostrophe in front of my data.
(apparently, it doesn't convert if my entries are '10-48 instead of 10-48.)

cheers

employee at bigpharma

"jamezog" wrote:

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!!

"Peo Sjoblom" wrote:

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

--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

"Peo Sjoblom" wrote:

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


--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

Excel Date Format - users should be able to override it automa
 

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


"bigpharma" kirjutas sõnumis news:
...
I work for a large pharmaceutical company and like many of you we have
loads
of data.

This is indeed the dumbest function ever invented. And there's no way of
turning off/over riding it.

I've spend an entire hour on the web, only to find out from this forum
that
Microsoft has not invented a way to turn this useless function off!!

When the data in the cell is '10-48' why can't it remain as that? and not
converted to October 1948?

Let me turn it off damn it!

and don't tell me to put a ^%#($*&^#@$ apostrophe in front of my data.
(apparently, it doesn't convert if my entries are '10-48 instead of
10-48.)

cheers

employee at bigpharma

"jamezog" wrote:

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!!

"Peo Sjoblom" wrote:

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

--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

"Peo Sjoblom" wrote:

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


--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

Excel Date Format - users should be able to override it automa
 

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

"bigpharma" wrote in message
...
I work for a large pharmaceutical company and like many of you we have
loads
of data.

This is indeed the dumbest function ever invented. And there's no way of
turning off/over riding it.

I've spend an entire hour on the web, only to find out from this forum
that
Microsoft has not invented a way to turn this useless function off!!

When the data in the cell is '10-48' why can't it remain as that? and not
converted to October 1948?

Let me turn it off damn it!

and don't tell me to put a ^%#($*&^#@$ apostrophe in front of my data.
(apparently, it doesn't convert if my entries are '10-48 instead of
10-48.)

cheers

employee at bigpharma

"jamezog" wrote:

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!!

"Peo Sjoblom" wrote:

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

--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.

"Peo Sjoblom" wrote:

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


--

Regards,

Peo Sjoblom


"jamezog" wrote in message
...
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.