Reformat date in Excel ...saved from csv file

[phospher]

Hi again,

The column dates from the csv file are formated
like this Thu,27 Feb, 2003 I prefer to look at the date this way 03/13/04. Can this be reformated? there are over 10k records.

Most greatful.

Phospher

 

[bluedragon2]

Try:

=DATEVALUE(MID(A1,FIND(",",A1)+1,2)&"-"&MID(A1,FIND(",",SUBSTITUTE(A1,",","*",1))-3,3)&"-"&RIGHT(A1,4))

and format the cell as date



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[phospher]

This did not work. I have tried it in a new column on either side of the date. Ive tried the Insert function Fx.
I am stumphed.

Phos

 

[bluedragon2]

Change the A1's in the formula with the cell that your date is in.

Also, If your single digit date looks like: Thu,7 Feb, 2003
it will not work, I will have to adjust it for those dates.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

also, if you are getting a 5 digit number, format that cell to date and it will display the date you want.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[phospher]

I've changed my dates from Thu,27 Feb, 2003 to 27 02, 2003
by using find and replace tool. Now would it be easier to reformat this vs the origional wordy Thu,27 Feb, 2003?


Phos

 

[SkipVought]

phospher,

Blue implied a VERY IMPORTANT ISSUE...

"are getting a 5 digit number" when you Format your date as GENERAL?

If NOT, you do not have a REAL DATE!



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[phospher]

I formated the cell(s) in the column as date and choose 03/14/03 style. I never format cells as general. My formated dates show 01/01/04 all 6 numbers filled in. Not 5. Sorry if I'm misunderstanding this. I'm still open for suggestions.

Most Greatful,

Phos

 

[SkipVought]

My post was prompted by your pervious statement...

"I've changed my dates from Thu,27 Feb, 2003 to 27 02, 2003"

I have seen users attempt to use string manipulation to format dates and not end up with dates at all. My point was that ALL DATES are just NUMBERS like...

today is 38069.

FORMAT it any way you like, it's STILL 38069.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884