Convert date from YYYY/MM/DD to MM/DD/YY
Linda
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" <njha...@optusnet.com.au> 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).
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Linda" <tc...@hmm21.com> wrote in message
news:049a01c347eb$7af2b940$a101...@phx.gbl...
> 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?
Don Guillett
--
Don Guillett
SalesAid Software
Granite Shoals, TX
don...@281.com
"Linda" <tc...@hmm21.com> wrote in message
news:057c01c347f9$fa72c440$a101...@phx.gbl...
Linda
(still indicates a negative date value).
>.
>
Norman Harker
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).
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Linda" <tc...@hmm21.com> wrote in message
news:057c01c347f9$fa72c440$a101...@phx.gbl...
Norman Harker
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).
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Linda" <tc...@hmm21.com> wrote in message
news:05b001c347ff$424ef810$a101...@phx.gbl...
Ricky Pang
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" <njha...@optusnet.com.au> wrote in message
news:etwjW$$RDHA...@TK2MSFTNGP12.phx.gbl...
Norman Harker
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)
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Ricky Pang" <rp...@telus.net> wrote in message
news:eI6WDdYS...@TK2MSFTNGP11.phx.gbl...
Ricky Pang
Ricky
"Norman Harker" <njha...@optusnet.com.au> wrote in message
news:OpRPFmYS...@TK2MSFTNGP10.phx.gbl...
Norman Harker
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)
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Ricky Pang" <rp...@telus.net> wrote in message
news:%23Gs0uvY...@TK2MSFTNGP10.phx.gbl...