VBA to convert string to date 2

[osx99]

I have the following string format contained within cells in excel

26Aug08
22Aug08

I need to convert these to dates to find the number of days between them so intend to use the DateDiff function

However, I'm having real problems converting these strings into a date format

I've tried the following which returns a Type mismatch error

statsDate = Range("f504").Offset(statsformcount, 0).Value
date1 = Format(CDate(statsDate), "dd/mm/yyyy")

Can anyone help on this one?

Thanks,
os

 

[SkipVought]

Hi,

No VBA required.

Use Data > Text to columns using the correct date y m d format.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Also, if you just want to see the number of days between the two, DateDiff is overkill. Just subtract one from the other.

Have a look at faq68-5827 for information on how Excel handles dates and times.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[DaveInIowa]

Use Skip's method if it fits your need, but if you have to do the conversion in code, you'll need to custom parse your specialized date format. Replace CDate with this function:

Public Function DateFromDDMMMYY(DDMMMYY As String) As Date
    On Error Resume Next
    DateFromDDMMMYY = 0 'Default return value to 12:00:00 AM
    DateFromDDMMMYY = CDate(Mid$(DDMMMYY, 3, 3) & " " & Left(DDMMMYY, 2) & " " & Right(DDMMMYY, 2))
End Function

Funny that 'Text to Columns...' is able to parse the value but 'CDate' can't.

 

[AnotherHiggins]

Even stranger is the fact that the worksheet function DateValue can convert this, but DateValue in VBA cannot.

But, similarly to Dave's solution, you could use:

dteStartDate = DateValue( _
        Left(Range("A1"), 2) & "/" & _
        Mid(Range("A1"), 3, 3) & "/" & _
        Right(Range("A1"), 2))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[osx99]

SkipVought - thanks for correct advice but I needed to use the result as part of a wider calc in other VBA code

anotherhiggins - spot on, it works a treat thanks!

DaveInIowa - thanks too!