Bookmark and Share

Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


From   "Phillips, Patrick" <[email protected]>
To   "[email protected]" <[email protected]>
Subject   st: Stata bug? - Mixture of European/US dates using import excel
Date   Fri, 7 Feb 2014 21:52:32 +0000

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/


© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index