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!

English(United States) Regional Option does not provide the yyyy/mm/dd
format in the list under date options but it does recognize that entry
as a date.

If you get ############# it's usually because your column isn't wide
enough.

Otherwise see my post of earlier (it's best to keep in the same
"thread" as things can get very confusing and can waste time of people
trying to help).



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

 

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

 

[Ricky Pang]

Hi,
I'm in the same situation as Linda with this date format problem. I have to
enter [37815], which is the 37,815th day from January 1900, just to get
[13-Jul-03]. My usual way of [13/7/03] just doesn't work. It results in
[0-Jan-00].

I've tried a few things already, but of no avail. Such as:
1) Format, Cells, Date, choose 14-Mar-01. Didn't work. I got [0-Jan-00].
2) Format, Cells, Custom, choose dd-mmm-yy. Didn't work. I still got
[0-Jan-00].
3) Put 1 in a cell, Edit, Copy, Select a date, Edit, Paste Special,
"Multiply". Didn't work...same result.
4) I've copied and pasted a cell from another worksheet that is formatted
as [13-Jul-03], but when I try to change the date, it results in [0-Jan-00]
again.

Strangely, [7/13/03] does work in other worksheets within my workbook. So
how do you get this worksheet to accept an entry of [7/13/03] to result in a
date format of [13-Jul-03]?

Much appreciated,
Ricky

 

[Norman Harker]

Hi Ricky!

Check the transition options.

I can replicate your problem using:

Tools > Options > Transition
place check in "Transition Formula Entry"

That then calculates 13 divided by 7 divided by 3 which is
0.619047619047619 which is 14:51:26 on 1-Jan-1900

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 14th July: France, French Guinea,
French Polynesia, Guadeloupe, New Caledonia, St Martin, and St. Pierre
& Miquelon (Bastille Day); Iraq, Martinique and Mayotte (National
Days); Madagascar (Fandoana Bathing Festival); Sweden (Crown Princess’
Birthday);Turkmenistan (Turkment Bakhsi Holiday); United Kingdom
(Emmeline Pankhurst Day). O-Bon / Festival of Souls (Shinto)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi,
I'm in the same situation as Linda with this date format problem. I have to
enter [37815], which is the 37,815th day from January 1900, just to get
[13-Jul-03]. My usual way of [13/7/03] just doesn't work. It results in
[0-Jan-00].

I've tried a few things already, but of no avail. Such as:
1) Format, Cells, Date, choose 14-Mar-01. Didn't work. I got [0-Jan-00].
2) Format, Cells, Custom, choose dd-mmm-yy. Didn't work. I still got
[0-Jan-00].
3) Put 1 in a cell, Edit, Copy, Select a date, Edit, Paste Special,
"Multiply". Didn't work...same result.
4) I've copied and pasted a cell from another worksheet that is formatted
as [13-Jul-03], but when I try to change the date, it results in [0-Jan-00]
again.

Strangely, [7/13/03] does work in other worksheets within my workbook. So
how do you get this worksheet to accept an entry of [7/13/03] to result in a
date format of [13-Jul-03]?

Much appreciated,
Ricky

Hi Linda!

English(United States) Regional Option does not provide the yyyy/mm/dd
format in the list under date options but it does recognize that entry
as a date.

If you get ############# it's usually because your column isn't wide
enough.

Otherwise see my post of earlier (it's best to keep in the same
"thread" as things can get very confusing and can waste time of people
trying to help).



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

 

[Ricky Pang]

Thank-you so much Norman. That's exactly it. Perfect!

Ricky

Hi Ricky!

Check the transition options.

I can replicate your problem using:

Tools > Options > Transition
place check in "Transition Formula Entry"

That then calculates 13 divided by 7 divided by 3 which is
0.619047619047619 which is 14:51:26 on 1-Jan-1900

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 14th July: France, French Guinea,
French Polynesia, Guadeloupe, New Caledonia, St Martin, and St. Pierre
& Miquelon (Bastille Day); Iraq, Martinique and Mayotte (National
Days); Madagascar (Fandoana Bathing Festival); Sweden (Crown Princess'
Birthday);Turkmenistan (Turkment Bakhsi Holiday); United Kingdom
(Emmeline Pankhurst Day). O-Bon / Festival of Souls (Shinto)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi,
I'm in the same situation as Linda with this date format problem. I have to
enter [37815], which is the 37,815th day from January 1900, just to get
[13-Jul-03]. My usual way of [13/7/03] just doesn't work. It results in
[0-Jan-00].

I've tried a few things already, but of no avail. Such as:
1) Format, Cells, Date, choose 14-Mar-01. Didn't work. I got [0-Jan-00].
2) Format, Cells, Custom, choose dd-mmm-yy. Didn't work. I still got
[0-Jan-00].
3) Put 1 in a cell, Edit, Copy, Select a date, Edit, Paste Special,
"Multiply". Didn't work...same result.
4) I've copied and pasted a cell from another worksheet that is formatted
as [13-Jul-03], but when I try to change the date, it results in [0-Jan-00]
again.

Strangely, [7/13/03] does work in other worksheets within my workbook. So
how do you get this worksheet to accept an entry of [7/13/03] to result in a
date format of [13-Jul-03]?

Much appreciated,
Ricky

Hi Linda!

English(United States) Regional Option does not provide the yyyy/mm/dd
format in the list under date options but it does recognize that entry
as a date.

If you get ############# it's usually because your column isn't wide
enough.

Otherwise see my post of earlier (it's best to keep in the same
"thread" as things can get very confusing and can waste time of people
trying to help).



--
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.
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.
Hello there,

I have downloaded information into an Excel spreadsheet
that contains dates that are in yyyy/mm/dd format, but
there isn't a format listed in Excel for the U.S. I
want
to convert the date to mm/dd/yy so that I can do
some "days between" type calculations. Any suggestions?

 

[Norman Harker]

Hi Ricky!

Thanks for thanks is always appreciated and shows lurkers and Google
searchers that a solution works.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 14th July: France, French Guinea,
French Polynesia, Guadeloupe, New Caledonia, St Martin, and St. Pierre
& Miquelon (Bastille Day); Iraq, Martinique and Mayotte (National
Days); Madagascar (Fandoana Bathing Festival); Sweden (Crown Princess’
Birthday);Turkmenistan (Turkment Bakhsi Holiday); United Kingdom
(Emmeline Pankhurst Day). O-Bon / Festival of Souls (Shinto)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Thank-you so much Norman. That's exactly it. Perfect!

Ricky

Hi Ricky!

Check the transition options.

I can replicate your problem using:

Tools > Options > Transition
place check in "Transition Formula Entry"

That then calculates 13 divided by 7 divided by 3 which is
0.619047619047619 which is 14:51:26 on 1-Jan-1900

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 14th July: France, French Guinea,
French Polynesia, Guadeloupe, New Caledonia, St Martin, and St. Pierre
& Miquelon (Bastille Day); Iraq, Martinique and Mayotte (National
Days); Madagascar (Fandoana Bathing Festival); Sweden (Crown Princess'
Birthday);Turkmenistan (Turkment Bakhsi Holiday); United Kingdom
(Emmeline Pankhurst Day). O-Bon / Festival of Souls (Shinto)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi,
I'm in the same situation as Linda with this date format

problem. I
have to

enter [37815], which is the 37,815th day from January 1900, just

to
get

[13-Jul-03]. My usual way of [13/7/03] just doesn't work. It results in
[0-Jan-00].

I've tried a few things already, but of no avail. Such as:
1) Format, Cells, Date, choose 14-Mar-01. Didn't work. I got [0-Jan-00].
2) Format, Cells, Custom, choose dd-mmm-yy. Didn't work. I

still
got

[0-Jan-00].
3) Put 1 in a cell, Edit, Copy, Select a date, Edit, Paste Special,
"Multiply". Didn't work...same result.
4) I've copied and pasted a cell from another worksheet that is formatted
as [13-Jul-03], but when I try to change the date, it results in [0-Jan-00]
again.

Strangely, [7/13/03] does work in other worksheets within my workbook. So
how do you get this worksheet to accept an entry of [7/13/03] to result in a
date format of [13-Jul-03]?

Much appreciated,
Ricky