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

G

Guest

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
 
F

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
 
G

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
 
G

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
 
G

Guest

Thanks. Frank. Your suggestion is quite helpful

Regards/Wintersunshine from Shanghai

“Frank Kabelâ€ç¼–写：
 
G

Guest

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â€ç¼–写：
 
G

Guest

Thanks, you helped me with my problem too!

Frank Kabel said:
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
Click to expand...
 
N

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
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert mm-dd-yy to mmm so pivot table reads mmm not mm-dd-yy 1
Returning the month (mmm) from cell in date format dd/mm/yyyy 1
change DD/MM/YY to MM/DD/YY 6
Date Format Not Responding to MM/DD/YY 2
Formula bar shows mm/dd/yyyy. To show dd/mm/yyyy. How? 2
convert Julian date to DD/MM/YYYY 7
Change from mm/dd/yy to yy/mm> 3
dd/mm/yy if i change to some other format 2

Top