convert 1/03/201112:00 PM from Excel into readable Stata format

[Elizabeth Unger]

Two questions:

1. Please provide code that correctly converts 1/03/201112:00 PM into readable Stata format. It is being read in from an excel file. Notice that it has AM and PM. Also, there is not a space between CCYY and the HH. I am not sure if that matters.

2. My hours are in military time with no minutes. I have read pdf instructions about formatting variables. When the new variable is created, the hours are expressed as 12:59 and then 13:01. I want to create a hourly time series graph that forces the ticks to be at Jan 1 12:00 PM, Jan 2 12 PM and so. When I plot the data, minutes are shown, Jan 1, 2011 12:06, Jan 2, 2011 15:15. My data does not have any minutes.

Fingers crossed this is straight forward.

Many thanks,
Icebetty

[Ulrich Kohler]

I would put a blank between the date and the time and then use the clock function:

Assuming that your hours are allways HH, the time part is the last 8 characters of your datestring variable:

. gen timestring = reverse(substr(reverse(datestring),1,8))

The timestring variable can be erased from datestring

. replace datestr = subinstr(datestring,timestring,"",.)

Than the two pieces can be concatenated with a blank in between

. gen clockstring = datestring + " " + timestring

which can then be converted into a clock variable:

. gen double eventime = clock(clockstring, "MDYhm")
. format eventtime %tc

It is possible to write all this in one line at the cost of readability.

Sorry, I did not get the point about military time and no minutes an all that. But perhapse the ideas above help to find a solution for this.

Uli