Not able to cahnge Date format

[narendrabr]

Hi
I am not able to change date format.
Like I need to convert it as yy/mm/dd format
Thanks

[benishiryo]

hi narendrabr, try:
=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))

or if your computer settings is D/M/Y, then:
=LEFT(A2,10)+0

in both cases, just format to what you want

[Special-K]

=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))
and copy down the column

[narendrabr]

Not possible from Format cells-Date/custom ?

Thanks!

[Kevin UK]

Hi narendrabr

Select your data > Text to Columns on the home tab > Delimited > Next > Next > Select: Date > Finish

Format the cells as in your original post: yy/mm/dd

[narendrabr]

Hi I am doing this but not changing..

Thanks

[Kevin UK]

Hi

I seem to have no issue with converting them to dates & times. See the attached file.

[narendrabr]

Yes doing the same but not changing.

Thanks

[Norie]

Try selecting DMY for the column data format on the 3rd step of Data>Text to columns...

[narendrabr]

I have tried Format cells and Text to columns but nothing works.

Thanks

[Norie]

How exactly did you try Data>Text to columns...?

Also, how did the formulas posted not work?

[narendrabr]

If I remove HH:MM:SS( 04:37:14) from date I can change format but if i have (24-03-2012 04:37:14) I cant.

Thanks

[Kevin UK]

Even when I use the following formula and format the cells I get no problem with the workbook you uploaded.

=DATEVALUE(LEFT(A2,10))+RIGHT(A2,8)

Is the data you are working with, the same as the file you uploaded!

[narendrabr]

Regional setting is US.

Do we have to use this formula to remove the time?


=DATEVALUE(LEFT(A2,10))+RIGHT(A2,8)

I am using the same sata/

Thanka

[martindwilson]

what is your date format in regional settings?

[martindwilson]

24-03-2012 is uk format thats probably your problem
try
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+RIGHT(A1,8) as was suggested in post #3

[narendrabr]

Ok.My question is not possible from format cells or text to columns functions in excel?

Thanks

[martindwilson]

you cant format that as your regional settings wont recognise the date so its treated as text
but
you could try
text to columns.fixed width
choose dmy for the first column click finish
column a will have dates col b times