date out of control
 

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

date out of control
 

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

date out of control
 

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

date out of control
 

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

date out of control
 

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

date out of control
 

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


"inquirer" wrote:

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

date out of control
 

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.

--
Regards,
Tom Ogilvy

"inquirer" wrote in message
...
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