All dates getting converted to 31Dec1899 when imported from excel to stata

[Rohit Kattamuri]

Hi everyone, I have a raw dataset (attached is a sample), and I prefer to edit/clean it only in Stata. When importing the file to stata, irrespective of the dates in the Excel file, all the date variable values are getting converted to 31Dec1899. It would be very helpful if someone could help me resolve this.

Thank you.

[Ken Chui]

See this: https://blog.stata.com/2011/01/05/us...ther-software/

There is a section about "Excel,1900 date system" that addresses this problem.

[Rohit Kattamuri]

Thanks, Ken. Excel is able to recognise the date =DATE(1989,1,2) and display correctly but in the format of 02/01/89. When the file is imported to Stata, all the columns are getting converted to strings due to strings in the first two rows. In addition, all the dates are becoming 31Dec1899 (string). It's not that the dates are adjusted to a certain reference point, but all are becoming the same exact date when imported.

If I change the format of date to 02/01/1989 in excel manually, then upon importing, the dates are shown correctly even though the variable is in string form. I'm wondering if it is possible to get this done without any operations in excel?

[Ken Chui]

I tried to replicate exactly as described and was not able to reproduce that error:

Excel file:

Import code (Stata 17):

import excel "Book1.xlsx", sheet("Sheet1") clear

And this is what I got:

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 A
"v1"       
"01feb1989"
end

As you can see the date was recognized correctly.

Since I cannot reproduce the error I can't be of much help. I would suggest:

• Try to export the Excel file into another format like csv text file and try to import that instead.
• When importing, try to use the "Cell range" function to exclude the first two rows so that the date may be recognized as numeric.