Wrong time format when importing datetime variable from Excel

[Elisabeth Laang]

Dear experts,

I have a problem with importing datetime variables from Excel into Stata – please help me!
Example: Consider the variable datetime for which an example observation in Excel reads '2016-01-31 19:33'. When importing the Excel data into Stata, this observation ‘value’ becomes '1/31/2016 07:33' in Stata. The date format is no problem, but the time is a major problem since it says 07:33 (and not 19:33 or 07:33 pm), making it impossible to tell if it is AM or PM. It thus seems as some information from Excel is lost, but I am sure it is still there somewhere.

The code I have been using reads:

clear all
import excel "excelfile", firstrow
generate double datetime_new = Clock(datetime, "MDYhm")
format datetimevar_new %tCDDmonCCYY_HH:MM

Can someone please tell me what I am doing wrong? I have tried finding the solution in manuals, Statalist, various search engines et cetera - but nothing for days!

Best,
Elisabeth

[Andrew Musau]

Can you provide an example of the original date variable using dataex, e.g., the first five?

dataex datetime in 1/5

It may just be the display format. Stata can display the same date using different formats. Compare the following:

di %tcnn/dd/ccYY_hh:MM 1769887980000
di  %tC 1769887980000

Res.:

. di %tcnn/dd/ccYY_hh:MM 1769887980000
  1/31/2016 7:33

.
. di  %tC 1769887980000
31jan2016 19:32:34

[Clyde Schechter]

I've not encountered this problem myself before, and I import date-time data from Excel fairly often in my work. I don't see anything wrong with your code that should be causing the problem, and I suspect this will have to go to Stata tech support for resolution. But before resorting to that, try this:

format datetimevar_new %tcDDmonCCYY_Hh:MM_AM

This would call on Stata to explicitly report 12-hour hours and AM/PM. I don't think this will actually fix the problem, but at least it will tell you whether the stored value of datetime_new is actually incorrect or is just being displayed wrong.

Added: Crossed with #2 which has a similar idea.

[Elisabeth Laang]

Can you provide an example of the original date variable using dataex, e.g., the first five?

dataex datetime in 1/5

It may just be the display format. Stata can display the same date using different formats. Compare the following:

di %tcnn/dd/ccYY_hh:MM 1769887980000
di %tC 1769887980000

Res.:

. di %tcnn/dd/ccYY_hh:MM 1769887980000
1/31/2016 7:33

.
. di %tC 1769887980000
31jan2016 19:32:34

I tried this, it displays it as 31jan2016 07:33:00. This is so strange... Thank you though!

[Elisabeth Laang]

I've not encountered this problem myself before, and I import date-time data from Excel fairly often in my work. I don't see anything wrong with your code that should be causing the problem, and I suspect this will have to go to Stata tech support for resolution. But before resorting to that, try this:

format datetimevar_new %tcDDmonCCYY_Hh:MM_AM

This would call on Stata to explicitly report 12-hour hours and AM/PM. I don't think this will actually fix the problem, but at least it will tell you whether the stored value of datetime_new is actually incorrect or is just being displayed wrong.

Added: Crossed with #2 which has a similar idea.

Trying this, it seems as the data is being stored wrong. I will look further into this (maybe there is something weird going on with the Excel file). Thank you!

[Andrew Musau]

Try to select the entire column in Excel, left click "Format Cells" \(\rightarrow\) "Date" and scroll down and select the date with 24 hour clock format. This will show you that Excel recognizes that each cell contains a date and it displays the date correctly. On Stata's side, update your installation and see if the problem persists.

update all

[Elisabeth Laang]

Try to select the entire column in Excel, left click "Format Cells" \(\rightarrow\) "Date" and scroll down and select the date with 24 hour clock format. This will show you that Excel recognizes that each cell contains a date and it displays the date correctly. On Stata's side, update your installation and see if the problem persists.

update all

That seems to solve the problem - thank you!