title: Ensure a valid date inside the cell in Excel
body: <p>I am trying to prepare a list of credit card transactions for pivoting and further analysis in Excel 2010 (for personal needs). Problem is that my banking institution does not follow a standard date format, so sometimes dates will be coming as <code>Jun. 1, 2013</code>, and sometimes as <code>May 27, 2013</code>. The first is not recognized as a date by Excel, so its Pivot cannot <em>Group</em> on that (month, quarter etc.). There are no other unrecognized date occurrences in the list.</p>
  
  <p><strong>Is there a way to ensure there is always a valid date in a column?</strong></p>
  
  <p>If I were coding in .NET, one option would be use <a href="http://msdn.microsoft.com/en-us/library/w2sa9yss%28v=vs.110%29.aspx" rel="nofollow">ParseExact</a> with a format string <code>MMM. d, yyyy</code>.</p>
  
  <p>Another option is to do an <code>If</code>, to convert any string to a date in a  something like:</p>
  
  <pre><code>If Not TypeOf MyCell Is DateTime Then _
    MyCell.Value = CDate(MyCell.Value.Substring(0,3) &amp;
                         MyCell.Value.Substring(3))
  </code></pre>
  
  <p>(This is VB.NETish pseudo-syntax, hope it helps explain my intention). It assumes that a non-valid date will always be a string with a dot inside it, to be removed by the converter - which I am okay with.</p>
  
  <p><strong>How would I do this in Excel?</strong> Looking for the most simple approach.</p>