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


--
Rob van Gelder - http://www.vangelder.co.nz/excel




"Chris Jakeman" wrote in message
m...
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