Thanks for your swift reply Rob, that fixed it. I'd still like to know why
what I had gave the "funny " result

Chris

"Rob van Gelder" wrote in message
...
Try DateSerial
Cells(ind, "a").Value = DateSerial(yy, mm, dd)


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"inquirer" wrote in message
...
Could anyone tell me why I can not control the date format in excel 2002
please
The problem I have is that I have dates created as
20010926
20010927
20010928
20011001
20011002
etc and I want to convert them to dates in the format
26/09/2001
27/09/2001
28/09/2001
01/10/2001
02/10/2001
etc
I have the following code
For ind = 2 To lastRow
yy = Left(Cells(ind, "a").Value, 4)
mm = Mid(Cells(ind, "a").Value, 5, 2)
dd = Right(Cells(ind, "a").Value, 2)

Cells(ind, "a").Value = dd & "/" & mm & "/" & yy
Next ind

When I run this the result is

26/09/2001
27/09/2001
28/09/2001
10/01/2001
10/02/2001

ie, all days <13 are rightjustified and formatted mm/dd/yyyy
and all other days are left justified and formatted dd/mm/yyyy

Why wont excel do as I tell it? - I want all my dates formatted as
dd/mm/yyyy and I dont care whether the are left or right justified.
Thanks
Chris