Sub ChangeDate()
Dim c As Range
For Each c In Range("A2:A1801").Cells
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2),
Right(c.Value, 2))
Next

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

wrote in message
ups.com...
Hi,
I have a spreadsheet with 1800 dates in one column. However, Excel
doesn't recognise them as a date. Why? Because they are in the
following format:

20050512

Which I know is yyyymmdd.

However, I cannot work out a way to convert these, in a macro, to
something that Excel recognises as a UK date (dd/mm/yyyy).

I've writtne a macro that rearranges the numbers, but the new data
doesn't recognise amiguous US/UK dates properly (ie is 20050512 the
fifth of December or the twelfth of May?):

Sub ChangeDate()
For Each c In Range("A2:A1801").Cells
' For each cell, re-arrange the numbers - last 2 digits, middle 2
and first 4, putting in a "/" between them
c.Value = Right(c.Value, 2) & "/" & Mid(c.Value, 5, 2) & "/" &
Left(c.Value, 4)
Next

Any help much appreciated....