title: How to format string as date -- Microsoft Project Export to Excel?
body: <p>I am exporting an Excel worksheet from Microsoft Project and trying to import that exported worksheet into an existing worksheet in Excel.  </p>
  
  <p>It's working correctly, however, there are string date representations (stored as <code>m/d/yy hh:mm AM/PM</code>) that just won't format correctly, even if I select <code>Format Cells</code> in Excel's GUI and try to format as a date.  The strings just don't change.  </p>
  
  <p>What can I do?  Is there code I can place in my import sub?  Is there a better way to export via Project so that the dates are formatted correctly already?</p>
  
  <p>Here is my current sub used for importing the worksheet (saved via Project):</p>
  
  <pre><code>Sub Import_from_Project()
      Dim wbSource As Workbook, wbDestination As Workbook
      Dim wsSource As Worksheet, wsDestination As Worksheet
      Dim DTAddress As String
  
      DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") &amp; Application.PathSeparator
  
      Application.ScreenUpdating = False
  
      Set wbSource = Workbooks.Open(DTAddress &amp; "Import.xlsx")
      Set wbDestination = ThisWorkbook
  
      Set wsSource = wbSource.Sheets(1)
      Set wsDestination = wbDestination.Sheets(6)
  
      'The range below is the data I'm copying
      'Only columns B and C contain the strings that I want to convert to date
      With wsSource
          .Range(.Range("A2"), .Range("C2").End(xlDown)).Copy wsDestination.Range("A3")
      End With
  
      'Pseudocode to loop through strings and convert to dates
      'With wsSource
      '    .Range(.Range("B2"), .Range("C2").End(xlDown)) .FORMAT TO DATE?
  
      wbSource.Close SaveChanges:=False
      ' wbDestination.Close SaveChanges:=True
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>I assume I would have to use the CDATE function, but I'm not sure how it fits in with the pseudocode above:</p>
  
  <pre><code>Format(CDate(stringDateRepresentation), "m/yyyy")
  </code></pre>
  
  <p>EDIT:  If it's important, I'm using Office 2010.</p>