Convert date from YYYY/MM/DD to MM/DD/YY

[Linda]

The "English (United States)" version does not provide a
yyyy/mm/dd date format. I've tried using one of the
other locales, such as "English (United Kingdom), that
has a sample of 2001-03-01, but the column then appears
as ##################, and says "Negative dates or times
are displayed as ##########..."

I also tried creating a custom type of yyyy/mm/dd and
yyyy-mm-dd, but they also displayed as ##############.

The field I am attempting to format is a numeric field.
Any other suggestions??


Subject: Re: How to identify and convert a yyyy/mm/dd
date to mm/dd/yy
From: "Norman Harker" <[email protected]> Sent:
7/11/2003 2:12:51 PM




Hi Linda!

I think I'm right in saying that yyyy/mm/dd is recognised
as a date by
all versions of Excel.

Are your dates being imported as dates or as text?

Try using =ISNUMBER(CellRefForOneDate)

If it returns TRUE then you have a date and all you have
to do is
apply a different format from the Format > Cells > Date
format list or
custom make your own using Format > Cells > Custom.

If it returns FALSE, then the date has been imported as
text.

Put 1 in a cell
Edit > Copy
Select your column of dates
Edit > Paste Special > Check "Multiply"
OK

That should return a date serial number that you can now
format to a
date.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Friday: Belgium (Flemish Community
Holiday and
Guldensporenslag); France (La Fete de la Magdalene);
International
(World Population Day); Italy (St. Rosalia Day); Mongolia
(Revolution
Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.

 

[Don Guillett]

Can't you custom format and widen the column

 

[Norman Harker]

Hi Linda!

Try the following:

Select a cell
Type the following
2003/7/12
Press Enter

What have you got in your pocketses (Sorry! cell).

Make sure you don't precede entry with an = sign.

My understanding is that that form of manual date entry is recognized
by all versions of Excel.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday: Bosnia-Herzegovina (Petrovdan); Canada,
Northern Ireland, and Eire (Orangemen's Day); Kiribati (Independence
Day); Malaysia (Birthday of the Yang di-Pertua Negi Pulau Pinang);
Micronesia (Micronesian Day); Sao Tome & Principe (Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.