Cell Format

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

CC

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.




-----Original Message-----
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.
Click to expand...
data.
 
Hi CC!

You can use a helper column with the formula:

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

Once you have the text entries you can

Copy
Edit > Paste Special > Values > OK

--
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.
 
Perfect! Tks...
-----Original Message-----
Hi CC!

You can use a helper column with the formula:

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

Once you have the text entries you can

Copy
Edit > Paste Special > Values > OK

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



.
Click to expand...
 
Hi CC!

Good to hear that you are sorted on this one now. Just remember the
need to pre-format as text.

--
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.
 
Back
Top