Thanks Rob - I thought a regional setting would take precedence over
everything otherwise why bother having one. It probably takes precedence
over everything so long as it isn't American
Chris
"Rob van Gelder" wrote in message
...
Excel and VBA are in love with the American date format.
A couple of examples.
'both examples assume user's regional settings are dd/mm/yyyy
Sub TestExcel()
Dim i As Long, dtm As Date, str As String
Columns(1).NumberFormat = "dd-mmm-yyyy"
dtm = "1-Jan-2004"
With Range("A1")
.Value = "'dd/mm/yyyy"
For i = 0 To 365 - 1
str = Format(dtm + i, "dd/mm/yyyy")
.Offset(i + 1).Value = str
Next
End With
With Range("B1")
.Value = "'dd/mm/yyyy"
For i = 0 To 365 - 1
str = Format(dtm + i, "mm/dd/yyyy")
.Offset(i + 1).Value = str
Next
End With
End Sub
Sub TestVBA()
Dim dtm As Date, str As String
'VBA looks at your regional settings
str = "01/12/2001"
dtm = str
MsgBox Format(dtm, "dd-mmm-yyyy")
'But if the regional settings don't match to this date it tries US
style
str = "01/13/2001"
dtm = str
MsgBox Format(dtm, "dd-mmm-yyyy")
End Sub
--
Rob van Gelder - http://www.vangelder.co.nz/excel
"inquirer" wrote in message
...
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