convert a string Date into a formated one

[sandoztek]

I have the string date "23Mar08" .
Can it be easily translated into a formated one (for arithmetic operations)

 

[SkipVought]

Hi,

Spreadsheet solution...
[tt]
select the columns of dates

Data > Text to columns - STEP 3 - Column Data Format - DATE: [RED]DMY[/RED]
[/tt]
VBA Solution...

    Dim s As String
    s = "23Mar08"
    s = Left(s, Len(s) - 5) & " " & Mid(s, Len(s) - 4, 3) & " " & Right(s, 2)
    MsgBox DateValue(s)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[LuAlPa]

If the format is fixed I would say this is even simpler
Dim s As String
s = "23Mar08"
s = Left(s, 2) & " " & Mid(s, 3, 3) & " " & Right(s, 2)
MsgBox DateValue(s)

 

[sandoztek]

Your solution is the one I finally reached too
(I used "-" instead of " ").
Thank you

 

[SkipVought]

Your solution ASSUMES, perhaps correctly, that the DAY is ALWAYS 2-digits.

BTW, this might also be informative...

faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sandoztek]

Yes, my code checks the format is 2 3 2 (ddmmmyy)