I have the following bit of code below that for some reason when run a
message box displays 01/02/2005 as it should but when the value is placed in
a cell it changes to 02/01/2005. I have tried this with month 03, 04 etc and
still the same. I have tried formatting the cell before hand and even
afterwards it thinks its 2nd Jan 2005 rather than 1st Feb 2005 (British
region).
Any ideas anyone???

Thanks
MC
---------------------------
Sub WRITEDATE()
Dim dateandtime As String

dateandtime = "01/02/2005 08:35"
date1 = Left(dateandtime, 10)
MsgBox date1 'to test
Cells(1, 1) = date1 'have tried cells(1,1).value=date1 but same results

End Sub
---------------------

use cDate

change
Cells(1, 1) = date1
to

Cells(1, 1) = cDate(date1)

cDate will observe your regional settings.

--
Regards,
Tom Ogilvy


"Mario" wrote in message
...
I have the following bit of code below that for some reason when run a
message box displays 01/02/2005 as it should but when the value is placed
in
a cell it changes to 02/01/2005. I have tried this with month 03, 04 etc
and
still the same. I have tried formatting the cell before hand and even
afterwards it thinks its 2nd Jan 2005 rather than 1st Feb 2005 (British
region).
Any ideas anyone???

Thanks
MC
---------------------------
Sub WRITEDATE()
Dim dateandtime As String

dateandtime = "01/02/2005 08:35"
date1 = Left(dateandtime, 10)
MsgBox date1 'to test
Cells(1, 1) = date1 'have tried cells(1,1).value=date1 but same results

End Sub
---------------------

that code you have written works perfect for me, 1/02/2005 in msgbox and in
cell
try to Dim dateandtime as date instead of string and have the time as a
seperate string and just put them together when needed


"Mario" wrote:

I have the following bit of code below that for some reason when run a
message box displays 01/02/2005 as it should but when the value is placed in
a cell it changes to 02/01/2005. I have tried this with month 03, 04 etc and
still the same. I have tried formatting the cell before hand and even
afterwards it thinks its 2nd Jan 2005 rather than 1st Feb 2005 (British
region).
Any ideas anyone???

Thanks
MC
---------------------------
Sub WRITEDATE()
Dim dateandtime As String

dateandtime = "01/02/2005 08:35"
date1 = Left(dateandtime, 10)
MsgBox date1 'to test
Cells(1, 1) = date1 'have tried cells(1,1).value=date1 but same results

End Sub
---------------------

Hi Markus,

I assume your problem is caused by the automatically
changing of a cell format within excel.

message box displays 01/02/2005 as it should but when the
value is placed in
a cell it changes to 02/01/2005. I have tried this with

Sub WRITEDATE()
Dim dateandtime As String

dateandtime = "01/02/2005 08:35"
date1 = Left(dateandtime, 10)
MsgBox date1 'to test
Cells(1, 1) = date1 'have tried cells(1,1).value=date1
but same results

Try range("A1").Value = date1

and go to Format = Cells = Number = Custom = TT/MM/JJJJ


Best

Markus


End Sub
---------------------
.

The cell contained the date Feb 1, 2005?

--
Regards,
Tom Ogilvy

"ben" wrote in message
...
that code you have written works perfect for me, 1/02/2005 in msgbox and
in
cell
try to Dim dateandtime as date instead of string and have the time as a
seperate string and just put them together when needed


"Mario" wrote:

I have the following bit of code below that for some reason when run a
message box displays 01/02/2005 as it should but when the value is
placed in
a cell it changes to 02/01/2005. I have tried this with month 03, 04 etc
and
still the same. I have tried formatting the cell before hand and even
afterwards it thinks its 2nd Jan 2005 rather than 1st Feb 2005 (British
region).
Any ideas anyone???

Thanks
MC
---------------------------
Sub WRITEDATE()
Dim dateandtime As String

dateandtime = "01/02/2005 08:35"
date1 = Left(dateandtime, 10)
MsgBox date1 'to test
Cells(1, 1) = date1 'have tried cells(1,1).value=date1 but same results

End Sub
---------------------

CDATE worked fine.

Thanks

MC

"Tom Ogilvy" wrote:

use cDate

change
Cells(1, 1) = date1
to

Cells(1, 1) = cDate(date1)

cDate will observe your regional settings.

--
Regards,
Tom Ogilvy


"Mario" wrote in message
...
I have the following bit of code below that for some reason when run a
message box displays 01/02/2005 as it should but when the value is placed
in
a cell it changes to 02/01/2005. I have tried this with month 03, 04 etc
and
still the same. I have tried formatting the cell before hand and even
afterwards it thinks its 2nd Jan 2005 rather than 1st Feb 2005 (British
region).
Any ideas anyone???

Thanks
MC
---------------------------
Sub WRITEDATE()
Dim dateandtime As String

dateandtime = "01/02/2005 08:35"
date1 = Left(dateandtime, 10)
MsgBox date1 'to test
Cells(1, 1) = date1 'have tried cells(1,1).value=date1 but same results

End Sub
---------------------