st: Stata bug? - Mixture of European/US dates using import excel

["Phillips, Patrick" <patrick.phillips@ucl.ac.uk>]

This is a problem that I had last year and have faced again recently. I hope it is just a problem with the setup on my computer, but I am concerned it is a bug with the command itself!

If a spreadsheet has a column with a mixture of dates and strings, Stata will read this column in as a string when using the import excel command.

If a date is stored in excel as 'text' it is read into Stata directly as string. 

However, if a date is stored in excel as a 'date' it is converted to mm/dd/yyyy format and read into Stata as a string. 

You can see that if dates are formatted in excel as dd/mm/yyyy, they will then be read into Stata in a mixture of mm/dd/yyyy and dd/mm/yyyy formats depending on whether excel 'thinks' it is text or a date! 

Try copying the following three lines into excel (the white space at the front of the second row is important) and saving as an excel spreadsheet.

20/02/2013
20/02/2013
Text

When I read this into stata it looks like the following:

     +-------------+
     |           A |
     |-------------|
  1. |   2/20/2013 |
  2. |  20/02/2013 |
  3. |        Text |
     +-------------+

Very worrying as it is impossible to tease out which might be dd/mm/yyyy and which mm/dd/yyyy! Has anyone else had this same problem? Does anyone have any solutions?

Thanks,

Patrick

_____________________________
Patrick PJ Phillips, PhD
Senior Statistician
MRC Clinical Trials Unit at UCL
Institute of Clinical Trials & Methodology
Aviation House
125 Kingsway
London  WC2B 6NH

Tel: +44 (0)20 7670 4637
Fax: +44 (0)20 7670 4815
Skype: patrick.pj.phillips
Website: http://www.ctu.mrc.ac.uk/
Twitter: @MRCCTU
_____________________________




*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/