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

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
0
1/5/2005 6:39:01 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
953 Views

Similar Articles

[PageSpeed] 56

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 


0
frank.kabel (11126)
1/5/2005 8:32:56 AM
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"
<wintersunshine@discussions.microsoft.com> 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

0
Gord
1/5/2005 6:08:26 PM
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"
<wintersunshine@discussions.microsoft.com> 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

0
Gord
1/5/2005 6:12:34 PM
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 
> 
> 
> 
0
1/8/2005 6:59:01 AM
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"
> <wintersunshine@discussions.microsoft.com> 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
> 
> 
0
1/8/2005 7:01:02 AM
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 
> 
> 
> 
0
Kristina (22)
7/5/2005 8:30:04 PM
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
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Kristina" <Kristina@discussions.microsoft.com> wrote in message 
news:563617E0-F954-4E76-88D0-6BF2B1405147@microsoft.com...
> 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
>>
>>
>> 


0
7/5/2005 8:44:10 PM
Reply:

Similar Artilces:

sorting date " MMM-YY" (Jan-05)
Hi, I have a spreadsheets with ticket numbers and the date it was submitted. It is in medium date format (3/18/2005). I have used this formula: =text(A2,"MMM-YY") to conver it to Mar-05. now i have a column with MMMYYY date, however, it will not let me sort it. It is sorting alphabetically, and not in the order of the date. so I have Feb-04,Feb-05, Jan-04,Jan-05 and so on. How can I sort this by month then year? Thank you! It is sorting alphabetically because the data is in TEXT format. In order to sort by date, you'll need to have the data formatted in some kind o...

Change date from mm-dd-yyyy to MMM-YY in Access
How do I change a field which contains a date in a mm-dd- yyyy format into mmm-yy format. For Example: Ship Month = 03/01/2004 Convert "Ship Month" value to display "Mar-04" Hi Vince, Set the Format property of the field, and/or the control(s) that are displaying it, to "mmm-yy" On Tue, 20 Apr 2004 12:01:52 -0700, "Vince" <anonymous@discussions.microsoft.com> wrote: >How do I change a field which contains a date in a mm-dd- >yyyy format into mmm-yy format. > For Example: Ship Month = 03/01/2004 > Convert "Ship Month&qu...

cell format, custom type: mmm-yy; working with
A cell is referenced in an equation. That cells format contains a custom type: mmm-yy. How does a vba function work with that generally? Is it a string to be chopped up and worked with, or can you use some object property to get at the value or month and year? Cate, If you want to use the formatted value of the cell, you need to use the ..Text property of the range object For example: Sub Test() Dim myC As Range Set myC = ActiveCell With myC .Value = Now MsgBox CDbl(.Value) & " is the cell's underlying number value" .NumberFormat = "mmm-yy" ...

mmm-yy issue
Hi there, How do you get excel to see a number written in a box as a number, and not in mmm-yy format as default. For example, when I open excel as a new sheet, I will type 2000-10, and it will auto format as oct-00, and in the tool bar it says 2000-10-01, when all I want is it to show 2000-10, and not any other format. I have been using a temperary solution of selecting all the cells and going to format cells > text to achieve this result, but excel doesn't save the setting so when I reopen the workbooks after entirely closing excel. I need to find a final solution, since it ...

Help: Can I change the date formula from mm/dd/yyyy to "mmm-yy" et
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 Hi sounds like your imported dates are not recognised as 'date' values but are stored as 'Text&...