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


(Chris Jakeman) wrote:

"Rob van Gelder" wrote in
message ...
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