Stop automatic formatting of cells (number, text, date, etc)

[Marcela]

How can I stop Excel2003 from automatically detecting and formatting a cell?

I downloaded some account information as an Excel file (only option
available). The financial institution shows the dates/times as this:
"5/30/2008 2:44:23 PM"

When I open the file, dates and times for March have the exact same format
as the webpage (5/30/2008 2:44:23 PM) and the cell is treated as "general".
But dates in June are displayed as "06/01/2008 20:44" and the cell is treated
as "personalized".

I want the June dates to be treated the same as March dates but nothing
works. If I change the cell format to "text", the content becomes a weird
number: 39453.7503472222. Same if I copy and paste the info into a
pre-formatted text cell. The only thing that works is to manually copy and
paste each value (using F2) but this is highly inefficient.... or doing a
bunch of search and replace in notepad.

Is there a way to prevent Excel from automatically detecting cell formats?
Thank you for any help you can provide

 

[ward376]

Dates and times in xl are just formatted numbers. The weird number you
see when you format as general is the actual value that represents the
date/time January 6, 2008 6:00 PM and 30 seconds.

Just format the dates and times the way you want after pasting. You
can use this custom format to make them all appear like your first
example: m/d/yyyy hh:mm:ss AM/PM.

If you change the dates/times to text, you won't be able to calculate
with them or change their appearance except manually.


Cliff Edwards

 

[Marcela]

Thanks for the formatting tip. I tried it but it does not completely work for
me. The am/pm part shows as "a.m." or "p.m." instead of "AM" or "PM", so it
is not consistent with the previous records. Also my system works with d/m/y
so Excel deciding that the 1st of June is the 6th of January does not work
for me.

If you change the dates/times to text, you won't be able to calculate
with them or change their appearance except manually.

I really don't need to do any calculations with them or change their
appearance any further. I just want them to be plain text.

But more important, I need Excel to stop applying this format automatically.
Is there any way to disable this?

Thank you

 

[ward376]

The only ways to keep xl from detecting and formatting dates AFAIK are
to either pre-format the range or enter a tick (apostrophe) with the
entry. Since you're downloading the info into xl, I don't think you'll
be able to do either of these.

I don't think I'm clear on what you're getting - you say your computer
is day first, but your example must be month first - since there's
only 12 months. You also say your example is for March, but May is the
5th month. Do all the dates/times you download change to "weird"
numbers when you format them as text? You may be able to use this to
gain consistency.

What financial institution are you downloading from? I'm surprised
they only have xl as an option, or really to have xl as an option at
all. Most have CSV, which is really text, but will open in xl. Try
opening your file with notepad and see what it looks like.

Cliff Edwards

 

[Marcela]

The only ways to keep xl from detecting and formatting dates AFAIK are

to either pre-format the range or enter a tick (apostrophe) with the
entry. Since you're downloading the info into xl, I don't think you'll
be able to do either of these.

OK. This is exactly what I was asking. That is, I can't change xl's behaviour.

I don't think I'm clear on what you're getting - you say your computer
is day first, but your example must be month first - since there's
only 12 months. You also say your example is for March, but May is the
5th month. Do all the dates/times you download change to "weird"
numbers when you format them as text? You may be able to use this to
gain consistency.

I am sorry, I meant May when I said March. What I get is a "general" cell
with this content for May 30th "5/30/2008 3:50:44 PM", if I change the cell
format to text, it stays the same. Excel does not consider this entry a date.
But for the next entry which is June 1st, Excel shows "06/01/2008 17:20",
the format is "personalized" and when I change it to text, it becomes
"39453.7228472222"
Since my system works with d/m/y, it does not recognize "5/30/2008" as a
date because there is no month "30", but it recognizes "6/1/2008" as a date
because "1" is January, therefore it applies the format.

What financial institution are you downloading from? I'm surprised
they only have xl as an option, or really to have xl as an option at
all. Most have CSV, which is really text, but will open in xl. Try
opening your file with notepad and see what it looks like.

I did it and it was even more strange than I expected.. it is an html file
with xls extension instead of htm.. isn't that weird? Anyway, at least now I
can add the tick marks.. thanks for the idea.