title: If then statement with date in VBA
body: <p>I asked a question this morning regarding changing the date format in VBA, and I've found a partial solution from other discussions:</p>
  
  <p>So I have the following dates (in ddmmyyyy format) in Excel</p>
  
  <pre><code>   A
  1/7/2018
  2/7/2018
  19/5/2018
  2/12/2018
  31/12/2018
  17/6/2018
  12/3/2018
  17/6/2018
  12/3/2018
  </code></pre>
  
  <p>I use the following code in VBA to change them to date:</p>
  
  <pre><code>Sub convert()
  
  Dim i As Long
  
  
  finalrow = Cells(Rows.Count, 1).End(xlUp).Row
  
   For i = 1 To finalrow
  
  Cells(i, 2).Value = CDate(Cells(i, 1))
  Next i
  
  End Sub
  </code></pre>
  
  <p>and obtained the following:</p>
  
  <pre><code>  A           B
  1/7/2018    1/7/2018
  2/7/2018    2/7/2018
  5/19/2018   19/5/2018
  2/12/2018   2/12/2018
  12/31/2018  31/12/2018
  6/17/2018   17/6/2018
  12/3/2018   12/3/2018
  6/17/2018   17/6/2018
  12/3/2018   12/3/2018
  </code></pre>
  
  <p>So my problem now is the original ddmmyyyy now becomes a messy combination of mmddyyyy and ddmmyyyy.</p>
  
  <p>I know the cdate have effectively changed the entire date format to mmddyyyy, but it confuses the date and month for some of the entries.</p>
  
  <p>NOTE: I also tried the popular jeeped's solution but it gives the same messy result.</p>
  
  <p>So now I'm thinking of a solution to use the if-then statement.</p>
  
  <p>If the DAY in the B column (contain mmddyyyy date) exceeds 12, then change the format to ddmmyyyy.</p>
  
  <p>I guess it's something like</p>
  
  <pre><code>With ActiveSheet.UsedRange.Columns("A").Cells
  .TextToColumns Destination:=.Cells(2), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
  .NumberFormat = "mm/dd/yyyy"   'change to any date-based number format you prefer the cells to display
  End With
  
  if (**the dd in mmddyyyy exceeds 12**) then 
  numberformat = "ddmmyyyy"
  
  end sub
  </code></pre>
  
  <p>So how do I set about doing that?</p>