Help needed changing date formats from m/d/y to d/m/y

[Craig Thomson]

I have a spreadsheet with a column of dates in the format ddd
m/d/yyyy, e.g. "Mon 9/29/2003". The data has come in as text as I have
cut and pasted it from another source.

I sort of have something working, but it only works for dates with 2
digits (i.e. from October (10th month) on and from the 10th of each
month on), e.g. "Wed 29/10/2003". The formula I have is:
=DATE(RIGHT(E3,4),MID(E3,6,2),MID(E3,9,2))

Can anyone help me get this to work for all dates?

Thanks in advance.

Craig

 

[J.E. McGimpsey]

An alternative:

Select your column.

Choose Data/Text To Columns. Select the Delimited radio button and
click Next. Check the Space Checkbox and click Next.

Select the first column (with "Mon" in it) and click the Do not
import radio button.

Select the second column and choose D/M/Y from the Date dropdown.
Click Finish.

Reformat your dates as you wish.

 

[Craig Thomson]

Thanks, that worked a treat!!

Craig

An alternative:

Select your column.

Choose Data/Text To Columns. Select the Delimited radio button and
click Next. Check the Space Checkbox and click Next.

Select the first column (with "Mon" in it) and click the Do not
import radio button.

Select the second column and choose D/M/Y from the Date dropdown.
Click Finish.

Reformat your dates as you wish.