Stata time export to Excel problem - missing values / wrong values

[adhoul]

I am trying to excel some data from a Stata project, to an Excel (.xlsx) file.

I have a time variable in Stata, which is of type float, and had the following format:

%tc_HH:MM:SS.sss

When I export the data to Excel, I get no values for the time.

When I copy paste the values, even though the value in Stata is " 01jan1960 17:01:00 ",

In excel it appears like it is 01:00.0,

but when I click on the cell it appears as 17:01:00

What is going on? Does anyone know how to solve this problem and properly export the time values to Excel? Thanks

[Nick Cox]

Often repeated advice is that date-time variables should be of type double so your data may already be corrupted.

If these data are just times of day then the maximum value is 86 400 000 and if your data are no more precise than the nearest second, then you may escape the precision problems that lie behind the advice to use double.

Your report is not consistent because a format %tc_HH:MM:SS.sss would not produce a display such as 01jan1960 17:01:00.

Either way, note that the values of such a variable are not string values such as "01jan1960 17:01:00"; that is just the result of a particular display format.

I can't comment on what you should do inside MS Excel. You could clarify what exactly you mean by "export the data to Excel": what commands did you use?

A reproducible example with example dataset and exact code would help. Please see http://www.statalist.org/forums/help#stata

(and also http://www.statalist.org/forums/help#realnames on our explicit policy that members give full names, i.e. given name and family name).

[Sergiy Radyakin]

Avoid COPY/PASTE.
Stata can export to Excel files:

clear
set obs 1
generate double t=61260000
format t %tcDDmonCCyy_HH:MM:SS

list
export excel using c:\temp\time.xls

Best, Sergiy