Cell Format

  • Thread starter Thread starter CC
  • Start date Start date
C

CC

When I type 03-2040 in a cell it automatically reformats
the text to 51196. With some other number combinations it
reformats to a date. Is there any way to turn off Auto
Formatting and how can I fix the format of existing data.
 
Hi CC!

The entry of 03-2040 is being interpreted as 1-Mar-2040

You need to pre-format the cells as text.

I don't think that you can fix the format after entry but you can
parse these rogue entries:

=TEXT(MONTH(A1),"00")&"-"&YEAR(A1)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks, I understand that it is interpreting 30-2040 as a
date and how to correct this for newly created
spreadsheets.

The issue that I am now faced with is lots of Excel
spreadsheets with tons cells that are incorrectly
formatted. As you know changing the format does not
recover the correct data.

Could this be an Excel version issue? My understanding is
that the person who had originally created this
spreadsheet was seeing 03-2040 not the date format.
Unfortunatly I do not know the deatils of their setup.
 
Back
Top