Converting excel date formats into stata format

[Yvonne Schmiedel]

Dear collegues,

I had to create some new date variables in Excel and would now like to transfer them into stata.

Having tried several formatting options, I did unfortunately not succeed in being able to work with the data.

As proposed in the Stata manual:

. gen statadate = exceldate + td(30dec1899) . format statadate %td

I did:

gen date_bc_pos = date_posbc_cand + td(30dec1899)


I am using Excel for Windows and Stata Version 13.


Do you have any advice on how to convert the date data into the correct stata format.

Many thanks,


Yvonne Schmiedel

[Svend Juul]

You don't tell how you transfer data from Excel to Stata. You also don't tell in which way your actions were unsuccessful, so I have to guess a bit.

I strongly discourage doing it by copy-and-paste; it is quite vulnerable to errors and mistakes. With Stata 13, use -import excel-; in most cases Excel dates (if they are formatted as dates) will be translated correctly to Stata dates - but you need to look carefully.

Another option is to import all variables as strings (see the -import excel- dialog) and next use Stata's date() (or daily()) function to translate a date string to date variable proper.

I think the formula you show is not from the Stata manual but from a blog written by Bill Gould. It tells about the numeric differences between Excel and Stata dates, but it is hardly the most practical way to do things.

[Yvonne Schmiedel]

Dear Svend,

many thanks for your reply. Sorry, yes I noticed - I forgot some details. Indeed, I did copy paste from excel to stata. I also dont like it. But I already imported a large dataset from Excel to stata and now I had to create some more date variables and may have to create some more... and add them into the existing dataset, so I felt copy paste would be the easiest way to do it. I found the give command in fact on 2 stata pages and at least one is from the manual.
The error message I received was/ is type mismatch.

But I believe I managed to do it in an easyway, which I thought does not work:

gen afstopdate =date(aflast_stopdate, "DMY")
format afstopdate %td


It seems to work so far.

Many thanks again.

Regards,

Yvonne

[Svend Juul]

OK. The main advice is: Take care. Transferring dates from one software to another is always risky. And whenever possible, use Stata's -merge- and -append- commands rather than adding new information to an existing dataset by copy-and-paste.