Another trick with Excel dates (assuming you have the software) is to
open the sheet in Excel and apply a general or numeric format to the
date column. This will show you that (at least for dates subsequent to
1/1/1900) Excel uses elapsed days as well*. All you need to do is
subtract Stata's Day 0 in Excel's number line, 21916 (1/1/1960) from the
Excel elapsed day and you'll get a number that, once imported into
Stata, can be formatted to show the correct date.
E.G.: Date = Aug 28th, 1963
Excel numeric equivalent: 23251 - 21916 = Stata's elapsed day 1335
. di %dM_d,_CY 1335
August 28, 1963
-JW
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Dan
Weitzenfeld
Sent: Thursday, August 28, 2008 4:01 PM
To: [email protected]
Subject: Re: st: converting dates from Excel .csv file
If your problem is just the leading zero issue, try:
replace date = "0" + date if length(date)==7
On Thu, Aug 28, 2008 at 7:12 AM, Michael McCulloch <[email protected]>
wrote:
> *********
> On 8/23 I submitted a question: > I have dates in an Excel *.csv file
as:
>>
>> date
>> 12/12/08
>> 12/02/08
>> 04/14/08
>> 04/04/08
>>
>> I wrote the following code to convert to Stata date:
>> gen str date_year = substr(date, -2,.)
>> gen str date_month = substr(date, 1, 2)
>> gen str date_day = substr(date, 4, 2)
>> destring date_year date_month date_day, replace
>> list date date_month date_day date_year
>>
>> However, when I import the *.csv file, the leading zeroes are
dropped
>> from months 1-9.
>> Is there a way to solve this in Stata, without having to create
>> separate columns for m, d, and y in Excel?
>
> *********
> On 8/24 Salah responded with the following advice, which unfortunately
is
> not successful (error msg: 60 missing values generated). I note that
Salah's
> code which he wrote in Stata 10.1 worked for him, but not for me; I'm
using
> 9.2. Any advice?
>
>
>
> Have you tried the date function?
>
> input str12 date
> "12/12/08"
> "12/02/08"
> "4/14/08"
> "4/04/08"
> end
> gen d=date(date, "MDY", 2010)
> format d %d
> list
>
> Tested using Stata 10.1
>
> *********
>
> *
> * For searches and help try:
> * http://www.stata.com/help.cgi?search
> * http://www.stata.com/support/statalist/faq
> * http://www.ats.ucla.edu/stat/stata/
>
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
------------------------------------------------------------
This transmission is intended for the sole use of the individual
and entity to whom it is addressed, and may contain information
that is privileged, confidential and exempt from disclosure under
applicable law. You are hereby notified that any use,
dissemination, distribution or duplication of this transmission by
someone other than the intended addressee or its designated agent
is strictly prohibited. If you have received this transmission in
error, please notify the sender immediately by reply to this
transmission and delete it from your computer.
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
