Date Formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brandon - 04 Mar 2008 13:07 GMT

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.

Reply to this Message

Ron Rosenfeld - 04 Mar 2008 13:42 GMT

>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

Reply to this Message

Brandon - 04 Mar 2008 14:29 GMT

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!

> >I need to open already created csv files - so formatting the cells beforehand
> >isnt an option.
[quoted text clipped - 10 lines]
> "Text".
> --ron

Reply to this Message

Ron Rosenfeld - 04 Mar 2008 15:16 GMT

>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

Reply to this Message

Ron Rosenfeld - 04 Mar 2008 15:30 GMT

>>Thanks, but that just messes the whole format up.
>
[quoted text clipped - 5 lines]
>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

Reply to this Message

Niek Otten - 04 Mar 2008 13:44 GMT

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.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Please can somebody help me.
|
[quoted text clipped - 16 lines]
| 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.

Reply to this Message

Brandon - 05 Mar 2008 06:39 GMT

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.

Reply to this Message

quiettechblue@yahoo.com - 15 Mar 2008 18:09 GMT

Brandon Brandon@discussions.microsoft.com left this in
microsoft.public.excel:

> Please can somebody help me.
>
[quoted text clipped - 18 lines]
> 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.)

Reply to this Message

Rate this thread: