date out of control

[inquirer]

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

 

[Rob van Gelder]

Try DateSerial
Cells(ind, "a").Value = DateSerial(yy, mm, dd)

 

[inquirer]

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]

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

 

[inquirer]

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

 

[keepITcool]

Maybe so..

avoid confusion by avoiding the value property and assigning a
dateserial to Value2 AND formatting yourself

Sub Dates()
Dim i%
Columns(1).NumberFormat = _
" ""Year:"" yyyy ""Day:"" dd ""Month:"" mm \(dddd \)"

For i = 1 To 365
Cells(i, 1).Value2 = DateSerial(2003, 12, 31) + i
Next
End Sub

see.. no problems.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Tom Ogilvy]

So if you wrote your code to work with your regional settings and then sent
your worksheet to another locality, you would want your code to fail because
their regional settings were different? Sounds like a plan.