inconsistent date conversion

[Chris Jakeman]

Fell into an Excel trap with date conversions. Anyone met this one
before?

The following code accumulates dates in a (potentially large) array
which is then copied to a worksheet in a single assignment.

Unfortunately, in the process Excel converts the dates to text but
does so inconsistently. The second date should be 12th May, but cell
contains 5th Dec.

My workaround is to use format() to do my own conversion as in the
fourth date.

(I'm using Excel v2002 and the UK locale but don't think this makes
any difference.)


Bye for now

Chris Jakeman


Option Base 1

Sub DateProblem()
Dim rData As Range
Dim aData(4, 1) As Variant

aData(1, 1) = DateSerial(2004, 1, 31) ' Shows as "1/31/2004"
aData(2, 1) = DateSerial(2004, 5, 12) ' Shows as "2004/Dec/05"
aData(3, 1) = DateSerial(2004, 5, 13) ' Shows as "5/13/2004"
aData(4, 1) = Format(DateSerial(2003, 2, 28), "yyyy/mm/dd")
With Worksheets("Date Conversion")
Set rData = .Range(.Cells(1, 1), .Cells(4, 1))
End With
rData.Value = aData ' Array copied to worksheet range
End Sub

 

[Rob van Gelder]

Chris,

I too have been trapped in the past with this sort of date issue.

The rule I've learned is to always write a cell using a Date variable.

If you try to write a cell using a String variable, you'll end up relying on
Excel's own date conversion, which leans toward US oriented date formatting.
In the case of "12/05/03", it becomes 5-Dec-2003.

If I have to use a String variable, I try to format the month as mmm.

Here's an example which highlights the issues (US computers will see the
same date, UK will see A1 being different from A2, A3)

Sub test()
Dim str1 As String, str2 As String, dtm As Date

str1 = "12/05/03" '12-May-2003
str2 = "12-May-03" '12-May-2003
dtm = DateSerial(2003, 5, 12) '12-May-2003

Range("A1").Value = str1
Range("A2").Value = str2
Range("A3").Value = dtm
End Sub

 

[Chris Jakeman]

Chris,

I too have been trapped in the past with this sort of date issue.

The rule I've learned is to always write a cell using a Date variable.

If you try to write a cell using a String variable, you'll end up relying on
Excel's own date conversion, which leans toward US oriented date formatting.
In the case of "12/05/03", it becomes 5-Dec-2003.

Here's an example which highlights the issues (US computers will see the
same date, UK will see A1 being different from A2, A3)

Reproduced as you predicted on my UK computer.

I used to use the Date type, but now I'm using an array to fill many
rows of a sheet in one assignment, I can't use it. This special
assignment only work for arrays of type Variant. I plan to continue
using an array in this application, because it's so much faster, but
I'll just have to take precautions.

Thanks for your help.

Bye for now,

Chris Jakeman
Tiny IT

 

[keepITcool]

you CAN use arrays with date type..

i use value2 to assign dates without excel interpretation
then use format to get what i like..


Sub tst()
Dim i%, dates(1 To 100, 1 To 1) As Date
For i = 1 To 100
dates(i, 1) = DateSerial(2004, 1, 1) + i
Next
With Cells(1, 1).Resize(100, 1)
.Value2 = dates
'silly format
.NumberFormat = "mm\|yyyy\|dd ddd"
End With
End Sub



keepITcool

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