Hi

I have a simple!?!? piece of code which grabs data from a 2d array:

MyCell.value=array(index1,index2)

The problem occurs when there's a data - Excel seems to flip the day and
month.

eg where array(index1,index2)="10/08/2005"

MyCell.value="08/10/2005"

I've tried reading the value to a string - the same happens, formatting the
string - the same happens, reading the value to a date -the same happens,
formatting the date - the same happens.

Where am I going wrong?

array(index1,index2)=cdate("10/08/2005")

--
Regards,
Tom Ogilvy


"interstellar" wrote in message
...
Hi

I have a simple!?!? piece of code which grabs data from a 2d array:

MyCell.value=array(index1,index2)

The problem occurs when there's a data - Excel seems to flip the day and
month.

eg where array(index1,index2)="10/08/2005"

MyCell.value="08/10/2005"

I've tried reading the value to a string - the same happens, formatting
the
string - the same happens, reading the value to a date -the same happens,
formatting the date - the same happens.

Where am I going wrong?

MyCell.NumberFormatLocal = "@"
MyCell.Value2 = "10/08/2005"

OR
MyCell.Value="'" & "10/08/2005

--
bighea

-----------------------------------------------------------------------
bighead's Profile: http://www.excelforum.com/member.php...fo&userid=2704
View this thread: http://www.excelforum.com/showthread.php?threadid=46680

Doh!

Many thanks Tom

"Tom Ogilvy" wrote:

array(index1,index2)=cdate("10/08/2005")

--
Regards,
Tom Ogilvy


"interstellar" wrote in message
...
Hi

I have a simple!?!? piece of code which grabs data from a 2d array:

MyCell.value=array(index1,index2)

The problem occurs when there's a data - Excel seems to flip the day and
month.

eg where array(index1,index2)="10/08/2005"

MyCell.value="08/10/2005"

I've tried reading the value to a string - the same happens, formatting
the
string - the same happens, reading the value to a date -the same happens,
formatting the date - the same happens.

Where am I going wrong?

You should be aware that all VBA uses US-style dates (mm/dd/yyyy, not dd/mm/yyyy), no matter what
your settings.

You can switch the day and month like so:

Dim myDate As String
myDate = "8/11/2005"
array(index1, index2) = _
DateValue(Day(DateValue(myDate)) & "/" & _
Month(DateValue(myDate)) & "/" & _
Year(DateValue(myDate)))

MyCell.Value = array(index1, index2)

HTH,
Bernie
MS Excel MVP


"interstellar" wrote in message
...
Hi

I have a simple!?!? piece of code which grabs data from a 2d array:

MyCell.value=array(index1,index2)

The problem occurs when there's a data - Excel seems to flip the day and
month.

eg where array(index1,index2)="10/08/2005"

MyCell.value="08/10/2005"

I've tried reading the value to a string - the same happens, formatting the
string - the same happens, reading the value to a date -the same happens,
formatting the date - the same happens.

Where am I going wrong?

Bernie,
Just for information:
cdate will convert the string in accordance with regional settings.

Datevalue won't as I recollect - so, seems to me, your solution would still
produce the wrong result with the ambiguous date string 10/08/2005
Then maybe I am missing something
--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
You should be aware that all VBA uses US-style dates (mm/dd/yyyy, not
dd/mm/yyyy), no matter what
your settings.

You can switch the day and month like so:

Dim myDate As String
myDate = "8/11/2005"
array(index1, index2) = _
DateValue(Day(DateValue(myDate)) & "/" & _
Month(DateValue(myDate)) & "/" & _
Year(DateValue(myDate)))

MyCell.Value = array(index1, index2)

HTH,
Bernie
MS Excel MVP


"interstellar" wrote in message
...
Hi

I have a simple!?!? piece of code which grabs data from a 2d array:

MyCell.value=array(index1,index2)

The problem occurs when there's a data - Excel seems to flip the day and
month.

eg where array(index1,index2)="10/08/2005"

MyCell.value="08/10/2005"

I've tried reading the value to a string - the same happens, formatting
the
string - the same happens, reading the value to a date -the same
happens,
formatting the date - the same happens.

Where am I going wrong?

Hi Interstellar,

I have a simple!?!? piece of code which grabs data from a 2d array:

MyCell.value=array(index1,index2)

The problem occurs when there's a data - Excel seems to flip the day and
month.

eg where array(index1,index2)="10/08/2005"

MyCell.value="08/10/2005"

I've tried reading the value to a string - the same happens, formatting the
string - the same happens, reading the value to a date -the same happens,
formatting the date - the same happens.

Where am I going wrong?

Excel will always try to interpret the date as a US MDY-order string. Use CDbl
before writing it to the cell:

MyCell.Value = CDbl(SomeDateStringInLocalFormat)

See my web site for (lots) more detail:
http://www.oaltd.co.uk/ExcelProgRef/Ch22/default.htm

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev