Convert string to date for VBA macro

[dktechguy112]

Hi,

This is my first time posting on the site. I'm new to VBA, and I'm sure this will be easy for you guys.

I am writing a VBA macro that will compare the dates in two columns, and place the difference (numbers of days between them) in a third column. I have a semi working version. This code works fine if the entries are of the type date. The problem is I'm trying to use the code on a workbook that has data in strings.
Many of the strings have a apostrophy in front of them. '02/25/2011
Sometimes the strings are formatted mm/dd/yyyy and sometimes they are formatted m/dd/yyyy

How can I convert these to dates so I can run my macro?
I would prefer to have a solution in VBA. The document I am working with has about 4000 rows.

Sub CalcDif()
  
  Dim StartDate    As Date
  Dim EndDate      As Date
  
  Dim i As Integer
  For i = 1 To 10
  StartDate = Cells(i, 1).Value
  EndDate = Cells(i, 2).Value
  Cells(i, 3).Value = EndDate - StartDate
  
  If Cells(i, 3).Value < 0 Then
  Cells(i, 3).Value = Cells(i, 3).Value * -1
  End If
  
  Next i
  
End Sub

 

[Pitchblend]

try reading the value into a string variable. When you assign the value to a string variable, VBA will strip out the apostrophe.
then convert the string to a date

Dim str1 As String
str1 = Cells(i,1).value
Dim startdate As Date
startdate = CDate(str1)