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