Problems with date format when reading external excel sheet

[kurtcjensen]

When I read a coloumn "Periode end" and store it in a different sheet
I have the problem that month and day is swapped if they are both <
13.

i.e if "Periode end" is 09-03-2009 in the below - 03-09-2009 will be
stored in the target cell

How to set the read date format?

Set rs_SOURCE = New ADODB.Recordset
rs_SOURCE.Open "Select [Alert], [Period end] From
[VTM,_monitorInstances_export$] where [Unit ID] =" & Right(ms_TEMP,
5), ExcelConn, _
adOpenDynamic, adLockOptimistic

'Update Date
.Cells(ml_TARGET_ROW, 10).Value = Format
(rs_SOURCE("Period end"), "dd-mm-yyyy")

 

[Incidental]

Hi There

You could try using cdate rather than formatting the date i.e.

..cells(ml_TARGET_ROW, 10).Value = CDate (rs_SOURCE("Period end"))
'where the data in the parenthesis is your date

Hope this helps

Steve