Exporting Date variables to Excel

[Aurelie Delannoy]

Hi,

I am using the new Stata14 release, and I am having difficulties with a code that used to work in the previous version of Stata. The code is for exporting quarterly dates variable into excel, so that the output read "2010q1" etc.

export excel using "ExcelOutput.xlsx", firstrow(variables) datestring("%tq")

I have 3 variables: year (YYYY), quarter(Q) and a "date" variable which combines the two and formatted as %tq. Previously, in excel, I would get the correct output, for instance showing:

date	year	quarter
2004q1	2004	1

But with the new Stata, it is doing something different, and the output now is:

date	year	quarter
5977q4	2004	1

Does anyone knows how to solve this? I must emphasize that whilst the excel output is incorrect as above, the date variable appears correctly in the Stata Data Editor. the change occurs only when exporting it to Excel.

Many thanks in advance for your help!

Aurelie.

[Hua Peng (StataCorp)]

In 05may2015 update, we fixed a bug in export excel:

"export excel, when exporting variables with any calendar date format other than %td, incorrectly exported the variables as a daily date instead of respecting the weekly, monthly, quarterly, half-yearly, or yearly format. This has been fixed."

Unfortunately, this fix introduced the unintended bad behavior Aurelie found when option "datestring()" is used. This will be fixed in the next Stata 14 update.

Before the update is released , you may use -export delimited- as a temporary work around.

export delimited using "ExcelOutput.csv"

[Aurelie Delannoy]

Thank you.

[Marvin Aliaga]

Going back to this post. I have stata 14 and I am having trouble exporting dates to excel. I have 3 date variables %td %tm %tw . I am trying to use the datestring option but I guess I am doing something wrong. I guess my command is wrong.

export excel using sample, firstrow(variables) datestring("%td","%tm", %tw") replace

Thanks,
Marvin

[Clyde Schechter]

I'm pretty sure that the -datestring- option only allows a single format to be specified and applies it to all date variables. To get what I imagine you want, I think you have to convert the variables to strings of the appropriate type first, and then do the export to Excel. -tostring- with an appropriate date format will get you there. -tostring- may balk at making the conversion claiming that the conversion is not reversible. I don't know why it does this, but you can apply the -force- option and it will proceed. Notwithstasnding -tostring-'s protests, the conversion is, in fact, reversible and information will not be lost.

[digression]
All of that said, I have never quite understood why Stata created the -datestring- option for this command. When you -export excel- without this option, if the variables carry date time formats in Stata, what comes out in Excel is an Excel internal numeric date, which is, in turn, displayed as a human readable date in Excel (unless you specifically apply a different format to it in Excel). This has the virtue that when you sort on that column in Excel, you get chronological sorting, rather than alphabetic sorting (which almost never makes sense) and you can do interval calculations, etc. So, I suppose it's nice to have choices, but this one strikes me as pointless. I'm probably missing something, probably because I'm not much of an Excel user and am unaware of things that work well with strings there.
[/digression]

[Marvin Aliaga]

Clyde, thank you so much for you response. I am learning a lot of new things lately since I got a new job and I am doing different things here. Stata is endless.Thank you!