Help: Can I change the date formula from mm/dd/yyyy to "mmm-yy" et

[wintersunshine]

Hi,

If the date was input as mm/dd/yyyy (12/31/2004), can I convert the format
to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to change date
format from "Format Cells-- Number tab--Category--choose "date" or "custom"
but just failed.

While Strangely, If i imput the data as "2004-12-31", i can change it to
whatever date format i like.

Appreicate your advice at earliest convenience.

Thanks

--
wintersunshine

[SMILE]

Hi
I think the problem is your input cell format is set to yyyy-mm-dd which you should change to mm/dd/yyyy to read the data as a date otherwise your input data is considered as a text only.

Toms

[Frank Kabel]

Hi
sounds like your imported dates are not recognised as 'date' values but are
stored as 'Text'. One possible workaound:
- select your date values
- goto 'Data - Text to columns'
- just step through the wizard and Excel should convert them to real date
values

--
Regards
Frank Kabel
Frankfurt, Germany

wintersunshine wrote:
> Hi,
>
> If the date was input as mm/dd/yyyy (12/31/2004), can I convert the
> format to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to
> change date format from "Format Cells-- Number tab--Category--choose
> "date" or "custom" but just failed.
>
> While Strangely, If i imput the data as "2004-12-31", i can change it
> to whatever date format i like.
>
> Appreicate your advice at earliest convenience.
>
> Thanks

[Gord Dibben]

sunshine

Depending upon your Windows Regional and Language Options, 12/31/2004 is
probably not a valid date entry and is text.

If short date in Windows>L&RO is set as mm/dd/yyyy then 12/31/2004 is a date.

If set as dd/mm/yyyy then 12/31/2004 is text and cannot be re-formatted.

Gord Dibben Excel MVP


On Tue, 4 Jan 2005 22:39:01 -0800, "wintersunshine"
<[email protected]> wrote:

>Hi,
>
>If the date was input as mm/dd/yyyy (12/31/2004), can I convert the format
>to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to change date
>format from "Format Cells-- Number tab--Category--choose "date" or "custom"
>but just failed.
>
>While Strangely, If i imput the data as "2004-12-31", i can change it to
>whatever date format i like.
>
>Appreicate your advice at earliest convenience.
>
>Thanks

[Gord Dibben]

sunshine

Forgot to propose a fix.....

Select the dates and Data>Text to Columns>Next>Next>>Column Data Format>Date.
Choose MDY and Finish.

Gord

On Tue, 4 Jan 2005 22:39:01 -0800, "wintersunshine"
<[email protected]> wrote:

>Hi,
>
>If the date was input as mm/dd/yyyy (12/31/2004), can I convert the format
>to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to change date
>format from "Format Cells-- Number tab--Category--choose "date" or "custom"
>but just failed.
>
>While Strangely, If i imput the data as "2004-12-31", i can change it to
>whatever date format i like.
>
>Appreicate your advice at earliest convenience.
>
>Thanks

[wintersunshine]

Thanks. Frank. Your suggestion is quite helpful

Regards/Wintersunshine from Shanghai

“Frank Kabelâ€?ç¼–åâ€*™ï¼š

> Hi
> sounds like your imported dates are not recognised as 'date' values but are
> stored as 'Text'. One possible workaound:
> - select your date values
> - goto 'Data - Text to columns'
> - just step through the wizard and Excel should convert them to real date
> values
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> wintersunshine wrote:
> > Hi,
> >
> > If the date was input as mm/dd/yyyy (12/31/2004), can I convert the
> > format to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to
> > change date format from "Format Cells-- Number tab--Category--choose
> > "date" or "custom" but just failed.
> >
> > While Strangely, If i imput the data as "2004-12-31", i can change it
> > to whatever date format i like.
> >
> > Appreicate your advice at earliest convenience.
> >
> > Thanks
>
>
>

[wintersunshine]

Thanks. Gord. First time to use this online discussion forum and found it's
quite efficient. I am still Excel beginner and far away to be a MVP

Regards/Wintersunshine from Shanghai


“Gord Dibbenâ€?ç¼–åâ€*™ï¼š

> sunshine
>
> Forgot to propose a fix.....
>
> Select the dates and Data>Text to Columns>Next>Next>>Column Data Format>Date.
> Choose MDY and Finish.
>
> Gord
>
> On Tue, 4 Jan 2005 22:39:01 -0800, "wintersunshine"
> <[email protected]> wrote:
>
> >Hi,
> >
> >If the date was input as mm/dd/yyyy (12/31/2004), can I convert the format
> >to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to change date
> >format from "Format Cells-- Number tab--Category--choose "date" or "custom"
> >but just failed.
> >
> >While Strangely, If i imput the data as "2004-12-31", i can change it to
> >whatever date format i like.
> >
> >Appreicate your advice at earliest convenience.
> >
> >Thanks
>
>

[Kristina]

Thanks, you helped me with my problem too!

"Frank Kabel" wrote:

> Hi
> sounds like your imported dates are not recognised as 'date' values but are
> stored as 'Text'. One possible workaound:
> - select your date values
> - goto 'Data - Text to columns'
> - just step through the wizard and Excel should convert them to real date
> values
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> wintersunshine wrote:
> > Hi,
> >
> > If the date was input as mm/dd/yyyy (12/31/2004), can I convert the
> > format to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to
> > change date format from "Format Cells-- Number tab--Category--choose
> > "date" or "custom" but just failed.
> >
> > While Strangely, If i imput the data as "2004-12-31", i can change it
> > to whatever date format i like.
> >
> > Appreicate your advice at earliest convenience.
> >
> > Thanks
>
>
>

[Nick Hodge]

Kristina

Frank tragically passed away a few months ago. I know however he would be
delighted to know he was still helping

Thank you

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"Kristina" <[email protected]> wrote in message
news:[email protected]...
> Thanks, you helped me with my problem too!
>
> "Frank Kabel" wrote:
>
>> Hi
>> sounds like your imported dates are not recognised as 'date' values but
>> are
>> stored as 'Text'. One possible workaound:
>> - select your date values
>> - goto 'Data - Text to columns'
>> - just step through the wizard and Excel should convert them to real date
>> values
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>> wintersunshine wrote:
>> > Hi,
>> >
>> > If the date was input as mm/dd/yyyy (12/31/2004), can I convert the
>> > format to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to
>> > change date format from "Format Cells-- Number tab--Category--choose
>> > "date" or "custom" but just failed.
>> >
>> > While Strangely, If i imput the data as "2004-12-31", i can change it
>> > to whatever date format i like.
>> >
>> > Appreicate your advice at earliest convenience.
>> >
>> > Thanks
>>
>>
>>