title: Excel conversion of US date (text) to european date (date)
body: <p>I am pasting data from a US webpage into an excel sheet. A European Excel cannot understand American dates.... so I paste it as Text.</p>
  
  <p>The difference between US and European dates is that the day and month is in a different order. How do I get from a "US text date" to real date that "European" Excel can understand? Reading it as dates doesn't work, so YEAR(), MONTH(), DAY() will not work on the native textstring. Some string conversion must be done first.</p>
  
  <p>The third column is just to to show the problem. That is the result af MONTH function called on text in column "A". It is taking the day for the month and therefore throws an error when reaching the 13th of September.</p>
  
  <pre><code>TEXT (from US web)       EU date (real date)            MONTH(A1)
  9/8/10 10:03 PM          8. Sep 2010                    8
  9/9/10 10:03 PM          9. Sep 2010                    9
  9/10/10 10:03 PM         10. Sep 2010                   10
  9/11/10 10:03 PM         11. Sep 2010                   11
  9/12/10 9:40 PM          12. Sep 2010                   12
  9/13/10 9:40 PM          13. Sep 2010                   ERROR
  9/14/10 9:40 PM          14. Sep 2010                   ERROR
  9/15/10 9:28 PM          15. Sep 2010                   ERROR
  </code></pre>
  
  <p>If there is no "conversion function" then a solution could be to split m/d/yy to three columns and create a date from that. If so I need some help with splitting the text into a day, month and  year.</p>
  
  <p>UPDATE with answer.... Lazarus came up with one that works. First, split the text in column A into Year, month and day. Then combine them eg. using the DATE function</p>
  
  <ul>
  <li>Year: <code>MID(A2,FIND("/",A2,FIND("/",A2,1)+1)+1,FIND(" ",A2,1)-FIND("/",A2,FIND("/",A2,1)+1)-1)</code></li>
  <li>Month: <code>MID(A2,1,FIND("/",A2,1)-1)</code></li>
  <li>Day: <code>MID(A2,FIND("/",A2,1)+1,FIND("/",A2,FIND("/",A2,1)+1)-FIND("/",A2,1)-1)</code></li>
  </ul>