Cant Change date on some rows

[ebbo]

I cant for the life of me figure out why I cant change the date format for some of these rows?

Im trying to modify a excel file sent to me by a client but some of the fields seem to be messed up.

Could somebody explain what i must do to get all the rows in a format

yyyy-mm-dd

i have already tried to format the cells, but only some rows update!!

[ChemistB]

Some of your values are stored as text. No amount of number formatting will change them. To convert them easily to numbers, select column A and Go "Text to Column" and "Finish". Does that help?

[sweep]

Hi,

Some of your rows aren't stored as an excel date. To convert them to an excel date, you could use something like this:

=DATE(RIGHT(A1,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,2))

and then format that cell according to your needs

[ebbo]

nope:/

[sweep]

Both work for me. What output are you getting to each suggestion?

[ebbo]

take a look at my new excel file. - there is a lot of values - i need a effective drag down method if possible!

Text to columns just doesnt seem to do anything. I tried text to columns then formatting but nothing happens.

Method 2 works better but I get #value on the rows that were updating before!

[sweep]

OK,

try this -

Please Login or Register  to view this content.

[ebbo]

cool works very well

2 more things if you dont mind pretty please.

1. If a row has an empty value (blank) how can avoid the #value

2. Instead of dragging all the way down with this (or any) formula is it possible to do some kind of shift click to populate. in very long lists like this is could take a while to drag down especially as I have quite a few of these lists to do!!

your help appreciated!!

cheers

[ChemistB]

Here's what the results were when I did "text to column" on your original file. It took all of 3 seconds. I wonder why it is not working for you.

[ebbo]

not really sure why it doesnt work for me! im using version 2003.

Can anyone help me with points 1 and 2 in my last post? pretty please!

thanks

[sweep]

To get rid of the error in a blank line:

=IF(A2="","",IF(ISNUMBER(A2),A2,DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))))

To copy down, double click the black dot in the lower right corner of the active cell.