Convert text to dates

[Chris AM]

Hi there,

I ahve the following problem:-

I currently have a spreadsheet that has a load of dates stored as text in
the following format:- 01.01.2008

I want to change it so that it sorts to 01/01/2008 but I cannot seem to do
it!! Changing the format to date makes no difference!!

Any help would be much appreciated.
thanks
Chris

 

[Barb Reinhardt]

Assuming your date is in A1, try this in an adjacent column.

=DATE(VALUE(RIGHT(A1,4)),VALUE(LEFT(A1,2)),VALUE(MID(A1,4,2)))

You can then sort on this column.

 

[Max]

Try Data > Text to Columns to convert it to real dates ..
Select the col of "dates", click Data > Text to Columns. Click Next > Next.
In step 3 of the wiz., check "Date" under Col data format, then select the
date format from the droplist, eg: MDY. Click Finish. That should convert it
all at one go to real dates recognized by Excel. Now you can sort the col,
format further to desired date format, etc.

 

[Barb Reinhardt]

I didn't realize that worked with dates formatted this way. I've done it
when dates are seen as text though. I've learned something today.

Thanks,
Barb

 

[Raj]

Hi Chris,

You can try the following simple steps as well.

Select all the dates entered in the format 01.01.08.

Goto - Edit
Click - Replace
Enter . in Find what?
Enter / in Replace with
Click - Replace All

That's it. all your dates text format would get changed to date
format. Hope it is the easiest shortcut,

regards,

Rajendran M