title: Excel changes dates where the day is lower than the month (e.g 2nd april) to the reverse (e.g 4th feb)
body: <p>I am having some toruble with excel and dates. The situation is as follows:
  We have a monthly report with a number of tabs which the macro imports into a central spreadsheet, it has about 120 columns. After importing, I need to add some formulas in the final few columns that are relevant to the data but not included in the data, and one of these is the period number (we work for a client with the year starting on April 1st and 13 periods of 4 weeks in the year). To acomplish this, I have a seperate sheet with all of the dates in column A, then periods and other data in the following columns. A simple Vlookup looks up the date and returns the period.</p>
  
  <p>However, some of the dates come stored as text (and so the vlookup has #N/A), simple fix, paste special - add a blank cell and voila it works. And this does work if I do it manually. But this is meant to do all the work for the user so I tried to do it programatically as per the code below:</p>
  
  <pre><code>'Sheets("dates and Periods").Activate
  'Range("P1").Copy
  'Sheets("Data").Activate
  'Range(Cells(2, 5), Cells(lastrow, 5)).PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
  'Range(Cells(2, 5), Cells(lastrow, 5)).NumberFormat = "dd/mm/yyyy"
  </code></pre>
  
  <p>I get the blank cell from "Dates and Periods" because I know it will definitely be blank. </p>
  
  <p>Now when I run this code, it works for most values. However for all dates in column E where the day is smaller than the month, e.g 1st April, 4th May etc, it switches the day and month around, and this is not formatting, by changing to long date I can see it has changed the data around.</p>
  
  <p>Is there a way around this? I have looked for a solution but all of the problems I could find when searching were to do with the 1904 date system or with formatting. </p>
  
  <p>Any help would be appreciated. </p>
  
  <p>Solution: [breaking it into parts and putting it back together]</p>
  
  <pre><code>For Each cell In Range(Cells(2, 5), Cells(lastrow, 5))
  
  cell.Value = Mid(cell, 4, 2) &amp; "/" &amp; Left(cell, 2) &amp; "/" &amp; Right(cell, 4)
  
  Next cell
  </code></pre>