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

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

=DATE(VALUE(RIGHT(A1,4)),VALUE(LEFT(A1,2)),VALUE(M ID(A1,4,2)))

You can then sort on this column.
--
HTH,
Barb Reinhardt



"Chris AM" wrote:

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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chris AM" wrote:
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

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


"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chris AM" wrote:
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

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






On Mar 4, 6:06 pm, Barb Reinhardt
wrote:
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

"Max" wrote:
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chris AM" wrote:
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