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

  • Thread starter Thread starter Craig Thomson
  • Start date Start date
C

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
 
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.
 
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.
Click to expand...
 
Back
Top