Pasting date into excel cell with the wrong effect.

[vin1]

I cant think of a proper title so i got something made that will shows the problem.


my code is something like this:

Private Sub Test123()

Dim Date_str As Variant
Dim TempCheck As Boolean


Date_str = "01-03-2012" ' = 1 March 2012


TempCheck = False
If Not Date_str = "" Then: TempCheck = True

If TempCheck = True Then
    Cells(1, 1) = Date_str
End If

End Sub

So what does it do?
A string is given as a date ("01-03-2012")
and when some condition is met it will paste its value into a cell on a worksheet.
but now i have the problem that the date is swapped from 01-03-2012 to 03-01-2012.
wich i don't want to happan.


P.S. my country is netherlands. "01-03-2012" is equal to 1 March 2012. it might be the clue why it is swapping its characters.

is there anyway to turn something off so it will not do this?

[kvsrinivasamurthy]

After this line,

Date_str = "01-03-2012" ' = 1 March 2012

Add this line in your code,

Date_str =mid(Dare_str,4,2) & "-" & Left(Dare_str,2) &  "-" & Right(Dare_str,4)

[vin1]

After this line,

Date_str = "01-03-2012" ' = 1 March 2012

Add this line in your code,

Date_str =mid(Dare_str,4,2) & "-" & Left(Dare_str,2) &  "-" & Right(Dare_str,4)

This is still fraqile to me. date can be putten in diffrent ways. ex(01 march 2012, 1 march 2012, 1-3-2012, 01-03-2012)
they are diffrent from each other.

Sub Test()
Dim Date_str As String
Date_str = "01-03-2012" ' = 1 March 2012
Range("A1") = CDate(Date_str)
MsgBox Month(Range("A1").Value) ' to prove that it is march
End Sub
CDate will convert string data type to Date data type

This looks good, now i can also check: If Date_str = "" wich i also needed actualy.

Thanks that solves it.

[smuzoen]

The best way to make sure the date is how you want is to use dateserial

Sub Test123()
Dim Date_str As Date
Date_str = DateSerial(2012, 3, 1)
Range("A1") = Date_str
MsgBox Month(Date_str) ' to prove that it is march
End Sub

I just added the message box to prove that the month is 3 i.e. march OR use CDate

Sub Test()
Dim Date_str As String
Date_str = "01-03-2012" ' = 1 March 2012
Range("A1") = CDate(Date_str)
MsgBox Month(Range("A1").Value) ' to prove that it is march
End Sub

CDate will convert string data type to Date data type