"import excel" reads in all the date variables as 31dec1899..

[John Kim]

Hi, please find attached, an Excel file that contains information about U.S. municipal bonds ("munis") issued in 1981. The first few columns include various date variables ("First Call Date" "Sale Date" "Final Maturity" etc.)

I'd like to import this Excel file (& many similar files for other years) into Stata. Strangely, "import excel" reads in all the date variables as 31dec1899.. The stranger thing is, if you change the original file in any way (e.g., delete a row, delete a column, add a space somewhere, etc.), and then try to import it into Stata, "import excel" reads in all the date variables as it should.

Does anyone have any idea about what's going on? I've never had this, and I'm struggling to figure out what's wrong.. I'd greatly appreciate any and all advice. Thank you!!

[Joseph Coveney]

Does anyone have any idea about what's going on?

I suspect that it's because the dates are all present as functions (Excel worksheet formulas), e.g., =DATE(1981,1,1), and not actual dates.

Are you able to get the data delivered to you in some other format than Microsoft Excel workbooks?

[John Kim]

Hi Joseph, thanks so much for your reply! Unfortunately, this is the only format I can get the data as.. I just checked what happens if I copy-paste values rather than formulas, and indeed, everything works. I think this is consistent with the fact that if you change the original file in any way, everything works fine. I presume I just have to do things manually?

[Joseph Coveney]

I presume I just have to do things manually?

You can automate the task if you know VBA (or even if you know how to use the Record Macro feature to generate a rudimentary macro that you can manually touch up afterward). If your many similar Excel workbooks for other years are named consistently, then the task will be easier, but you can generate a list of them if not.

You are aware that there are inconsistencies in the contents of at least the date columns, for example, the "Maturity Year" (Column O) in Rows 15 and 31, there are two entries in one cell, one "None" and a string date and the other two (different) string dates. I stumbled across those almost immediately and I see now that there are others as well. It promises to be a real cleanup operation down the road.