Date Formatting

[Brandon]

Please can somebody help me.

I need to disable date formatting in Excel 2003 & 2007.
Excel's "helpfullness" is being VERY unhepfull at the moment. It is changing
my style codes to dates, and if you format the cell to text then it applies
some useless number that means nothing to me instead of what is supposed to
be in that cell.

I need to open already created csv files - so formatting the cells beforehand
isnt an option.


Please can somebody tell me how to disable date formatting entirely in Excel.

The application dumps data to csv files. Users manipulate this data and work
with it on an ongoing basis.

What happens is this: One of the fileds that are exported is a stock code,
and excel changes this stock code to a date. The actual value in the stock
code is 3522-01 and Excel changes this to Jan-22. If you try and format the
cell to text, you get 592425 . So there is no way to change it.

 

[Ron Rosenfeld]

I need to open already created csv files - so formatting the cells beforehand
isnt an option.


Please can somebody tell me how to disable date formatting entirely in Excel.

One way around your problem -- and it can all be done using a VBA Macro, if
necessary:

1. Change the file name suffix from ".csv" to ".txt"
2. Open it in Excel.
3. The Text Import wizard will appear.
4. In Step 3, you will have the opportunity to define selected columns as
"Text".
--ron

 

[Niek Otten]

Rename the file to a .TXT file before importing.
This will give you a menu when importing, so you can choose the formats of the fields.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Please can somebody help me.
|
| I need to disable date formatting in Excel 2003 & 2007.
| Excel's "helpfullness" is being VERY unhepfull at the moment. It is changing
| my style codes to dates, and if you format the cell to text then it applies
| some useless number that means nothing to me instead of what is supposed to
| be in that cell.
|
| I need to open already created csv files - so formatting the cells beforehand
| isnt an option.
|
|
| Please can somebody tell me how to disable date formatting entirely in Excel.
|
| The application dumps data to csv files. Users manipulate this data and work
| with it on an ongoing basis.
|
| What happens is this: One of the fileds that are exported is a stock code,
| and excel changes this stock code to a date. The actual value in the stock
| code is 3522-01 and Excel changes this to Jan-22. If you try and format the
| cell to text, you get 592425 . So there is no way to change it.
|
|

 

[Brandon]

Thanks, but that just messes the whole format up.
Is there no setting in Excel or in the registry that will allow me to
disable automatic formatting of cells.

Thanks!

 

[Ron Rosenfeld]

Thanks, but that just messes the whole format up.

Why, or how?

Is there no setting in Excel or in the registry that will allow me to
disable automatic formatting of cells.

No
--ron

 

[Ron Rosenfeld]

Why, or how?


No
--ron

Actually, if you set the Tools/Options/Transition options, then the entries
will be evaluated as a formula, rather than as a date. But that won't help,
either because, for example 3522-01 will show up as =3522-1 in the formula
bar, and 3521 in the cell.

If ALL of your -nn values were two digits, with either a leading zero or no
zero, you could write a VBA macro to process it back. But that would
definitely require having a very defined format for these strings.

Seems to me that a properly processed .txt file would be simpler
--ron

 

[Brandon]

Thanks for all of your replies.
I will now consider it a fact that certain features in Excel cannot be
disabled and work around them.
Thanks for your help.

 

[quiettechblue]

Brandon (e-mail address removed) left this in
microsoft.public.excel:

Please can somebody help me.

I need to disable date formatting in Excel 2003 & 2007.
Excel's "helpfullness" is being VERY unhepfull at the moment. It is
changing my style codes to dates, and if you format the cell to text
then it applies some useless number that means nothing to me instead
of what is supposed to be in that cell.

I need to open already created csv files - so formatting the cells
beforehand isnt an option.


Please can somebody tell me how to disable date formatting entirely in
Excel.

The application dumps data to csv files. Users manipulate this data
and work with it on an ongoing basis.

What happens is this: One of the fileds that are exported is a stock
code, and excel changes this stock code to a date. The actual value in
the stock code is 3522-01 and Excel changes this to Jan-22. If you try
and format the cell to text, you get 592425 . So there is no way to
change it.

Try putting the problem field in quotes of some kind. (forcing the
string interpretation.)