problem with importing dates from excel

[Yealin Chung]

Dear experienced users of this group,

please help!

Importing an excel file into Stata, I have noted all the observations are being imported as string.
for majority, I can use a simple command to convert from string to numerical, if indicated.

But for dates, how should I format it?

To give you an example,

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
trialno         str12   %12s                  trialno
trialsite       str3    %9s                   TrialSite
sex             str3    %9s                   Sex
ethnicgroup     str7    %9s                   EthnicGroup
dateofconsent   str10   %10s                  DateOfConsent
dateoffirstco~n str10   %10s                  DateOfFirstConsultation
ageatextract    str4    %9s                   AgeAtExtract
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sorted by: 

variables that represent dates are:

dateofconsent
dateoffirstconsultation

which are imported as str10 variables

when I browse the observations - they are displayed in MM/DD/YYYY format.

I guess my question is

How can I edit this variable so that it displays the date as DD/MM/YYYY and also, formatted in such as way that allows me to calculate the difference between two dates.

Hope that makes send.

As always, thank you for your help!

[Fei Wang]

Yealin, help datetime and you will find many useful commands for handling dates and time. For your case, it would be

gen dateofconsent_new = date(dateofconsent, "MDY")
format dateofconsent_new %tdDD/NN/CCYY

[Nick Cox]

Fei Wang gave great advice. I would tweak it slightly. The function name date() is a hangover from early versions of Stata in which daily dates were the only kind of date given special support. And, to be fair, it matches ordinary English usage to the extent that what's the date?, at least around here, would probably get an answer like "22 August", namely a daily date.

But, that said, I recommend (using and recommending) the equivalent function daily() -- because the opposite applies, namely people often jump to the conclusion that date() is a general purpose function for creating any kind of numeric date, such as monthly, which isn't the case at all.

[William Lisowski]

Adding to the advice from Fei Wang in post #2, Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

[Yealin Chung]

Thanks guys. I am not a statistician by background, so find many of the recommended read to be quite daunting and I simple don't have the intelligence or brain capacity to understand! I have done a cheat's way, by converting .xlsx into .csv file then importing into Stata that way. That appears to have worked some how, and another advantage that it allows me to specify how each variable is imported , which .xlsx does not allow me to do. Thank you all though for all of your replies and help.