title: Excel VBA - Date Format Conversion
body: <p>I have come across a challenging task which I am not able to solve using many workarounds.</p>
  
  <p>In one column I have dates, the date can be in following three formats:</p>
  
  <blockquote>
    <p>1) Simple dd/mm/yy</p>
    
    <p>2) dd/mm/yy but may have words "before,after or about" around it. Any
    one of it and we just need to delete those words in this case.</p>
    
    <p>3) Date in a numeric format. A long decimal values like 1382923.2323
    but actually I can get a date from it after conversion.</p>
  </blockquote>
  
  <p><strong>The file is uploaded here. <a href="https://docs.google.com/file/d/0B7gzonuQsNbvQXNzWFhta0ZqUWs/edit?usp=sharing" rel="nofollow" title="date_format_macro_link">Date_format_macro_link</a></strong></p>
  
  <p>I wrote the following code but it's giving wrong results.</p>
  
  <pre><code>Sub FormatDates_Mine()
      ManualSheet.Activate
      ManualSheet.Cells.Hyperlinks.Delete
      ManualSheet.Cells.Interior.ColorIndex = xlNone
      ManualSheet.Cells.Font.Color = RGB(0, 0, 0)
  
      lastRow = ManualSheet.Range("A" &amp; Rows.Count).End(xlUp).Row
      Col = "A"
      For i = 2 To lastRow
          Cells(i, Col) = Trim(Replace(Cells(i, Col), vbLf, "", 1, , vbTextCompare))
  
          If InStr(1, Cells(i, Col), "about", vbTextCompare) &lt;&gt; 0 Then
              Cells(i, Col) = Trim(Replace(Cells(i, Col), "about", "", 1, , vbTextCompare))
              Cells(i, Col).Interior.Color = RGB(217, 151, 149)
          End If
  
          If InStr(1, Cells(i, Col), "after", vbTextCompare) &lt;&gt; 0 Then
              Cells(i, Col) = Trim(Replace(Cells(i, Col), "after", "", 1, , vbTextCompare))
              Cells(i, Col).Interior.Color = RGB(228, 109, 10)
          End If
  
          If InStr(1, Cells(i, Col), "before", vbTextCompare) &lt;&gt; 0 Then
              Cells(i, Col) = Trim(Replace(Cells(i, Col), "before", "", 1, , vbTextCompare))
              Cells(i, Col).Interior.Color = RGB(228, 109, 10)
          End If
  
          DateParts = Split(Cells(i, Col), "/", , vbTextCompare)
  
          Cells(i, Col) = Format(Cells(i, Col), "dd/mm/yyyy")
      Next i
  
      Range("D:E").HorizontalAlignment = xlCenter
  End Sub
  </code></pre>
  
  <p><strong>The file is uploaded here. <a href="https://docs.google.com/file/d/0B7gzonuQsNbvQXNzWFhta0ZqUWs/edit?usp=sharing" rel="nofollow" title="date_format_macro_link">Date_format_macro_link</a></strong></p>
  
  <p>Please help!</p>