Convert DD. December yyyy string into date?

[JKB]

Hi everybody!

I have a VBA problem, i would lik to convert the following string into the short date format: "1. december 2015"

I have tried the obvious solution, which would be to use the CDATE function, but it gives me a mismatch back. Is it possible that a smart way of doing this exists? I would prefer to avoid a loop

Cheers!

JKB

[p45cal]

Text-To-Columns:
Step 1: Delimited
Step 2: no boxes ticked
Step 3: Date, DMY
click Finish.
Now it's a proper Excel date you can format it as you want.
Worked here.

ps. you never did provide a file here: http://www.vbaexpress.com/forum/show...ed!&highlight=

[JKB]

Okay that works, but it's not very pretty in a code!
If i were to use text-to-columns, it would be better to use on a string, such that when i insert the value in the sheet, it is already in the date format?

[p45cal]

What is it you're doing? - a bit more info; where's the string coming from?
ps. the code's as simple as:
Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)

[JKB]

Okay i see that i used a lot of extra arguments! I used this.... (Works but ugly)

range("A1")TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True

I will use yours instead!

The reason why i would like to do it before pasting, is that my date is in an if statement, and only if my condition holds (which it rarely does), will i have to convert the date using fx text to columns.

But the part where im pasting the date into my sheet is not part of this loop, which defines the date

[snb]

Sub M_snb()
    MsgBox FormatDateTime(Replace("1. december 2015", ".", ""), 2)
End Sub

[JKB]

Sub M_snb()
    MsgBox FormatDateTime(Replace("1. december 2015", ".", ""), 2)
End Sub

Exactly what i was looking for!!! Thx And sorry about the cursing.