title: Dates in Table are submitted in incorrect regional format
body: <p><strong>Background Info</strong>
  I currently have a VBA user form, which submits daily KPIs to an Excel Table in Excel 2010. The Form is setup to put today's date into a text box in the format DD/MM/YYYY (I'm in Australia), then transfer that information to the Excel worksheet when I hit submit. Previously, this process has worked without a hitch, as it is just transferring pure text. But now, the date is being submitted in US format (MM/DD/YYYY). I'm pretty sure the problem is somewhere in Excel, but here is my VBA code anyway: </p>
  
  <pre><code>  'First it populates the text box    
      Private Sub UserForm_Activate()
           TextBox1.Text = Format(Now(), "DD/MM/YYYY")
           End Sub
      'Then it transfers to the spreadsheet
       With nwb.Sheets("daily_tracking_dataset_master")
          .Cells(emptyRow, 1).Value = TextBox1.Value
  </code></pre>
  
  <p><strong>Main Problem</strong>
  Somewhere on the Excel side, the data is now being submitted incorrectly in the US date format, so a submission on the fourth of June, becomes the sixth of April, see image: <a href="http://tinypic.com/r/2vslyd5/8/" rel="nofollow">http://tinypic.com/r/2vslyd5/8/</a>.</p>
  
  <p>When i change the number format to General, it shows that the pure text date from the VBA form has been converted to the April date. I.e. it's not a simple matter of changing the formatting, the date has actually been completely changed:</p>
  
  <ul>
  <li>41789 (30/05/2014) </li>
  <li>41789 (30/05/2014) </li>
  <li>41789 (30/05/2014) </li>
  <li>41735 (06/04/2014) </li>
  <li>41735 (06/04/2014) </li>
  <li>41735 (06/04/2014)</li>
  </ul>
  
  <p>I have tried several workarounds for this, including:</p>
  
  <ol>
  <li>Highlighting column A and changing the format to Date in the format DD/MM/YY</li>
  <li>Selecting Date format under the Home tab -> Number group -> Date drop down box</li>
  <li>Checking regional settings in Windows and Excel</li>
  <li>I have also tried going into Data -> Data Tools -> Text to columns. Then on step 3 selecting DMY under Date - this seems to be one of the main fixes for the issue I'm having in other forums. </li>
  </ol>
  
  <p>By the nature of the Excel Table, each time my submission form adds a record, the table is extended by one row - I'm not sure whether this would have any influence and the incorrect date settings are generated from the Table somehow?</p>
  
  <p>Any ideas for troubleshooting this would be fantastic.</p>