Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: st: Date format export to excel


From   "Ashim Kapoor" <ashimkapoor@gmail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Date format export to excel
Date   Thu, 11 Dec 2008 16:59:43 +0530

Hello Neil / Jeff.

Thank you for your reply. Funnily, the 1st line of dates is fine , in
the 2nd line onwards I observe what Jeph is talking about. Maybe there
is a reason for that too.

Also, I noticed one more thing. Suppose I DO NOT outsheet. I simply
copy and paste from the Stata Data Editor to Excel!!!! The dates work
out FINE!! Excel automatically takes the 1st day of each month which
leads me to think that Jeph's suggestion to have the data in % td form
as compared to % tm form makes sense.

Thank you,
Ashim

On Wed, Dec 10, 2008 at 6:57 PM, Jeph Herrin <junk@spandrel.net> wrote:
>
> Excel is reading the mon/year as mon/day and then adding
> the current year.
>
> So Nov 08 -> 11/08 -> 11/08/2008
>   Sep 01 ->  9/01 ->  9/01/2008
>
> Which explains what you are seeing, but not how to fix it.
>
> I have often run into similar problems with Excel, and how
> it handles dates differs not only with version of Excel but
> also how some of the defaults are set - dates that look fine
> when I open them in Excel are completely wrong when someone
> else opens the same file in Excel.
>
> When I recently needed to export Mon/Year to Excel, I converted
> all the months in Stata to dates (so, for example, Nov 08 to
> Nov 1, 2008), exported with -outsheet-, and then after importing
> the .csv file into Excel, changed the format to Mon/Year (so
> the day of month wasn't visible). This may or may not work for
> you.
>
> hth,
> Jeph
>
>
>
>
>
> Ashim Kapoor wrote:
>>
>> Hello Neil,
>>
>> Thank you for your reply.
>>
>> For example the number 586 which under the format %tm mean November
>> 2008 in Stata becomes 11/8/2008 in excel, where does excel choose this
>> 8th day from ?
>>
>> But more than this some dates get even the month wrong like for
>> example the number 500 which is september 2001, becomes 9/1/2008. The
>> year changes!!!
>>
>> Does this seem like a simple problem to fix ? Anything like this have you
>> seen?
>>
>> Thank you,
>> Ashim.
>>
>> On Wed, Dec 10, 2008 at 4:59 PM, Neil Shephard <nshephard@nhs.net> wrote:
>>>
>>> Ashim Kapoor wrote:
>>>>
>>>> Hello everyone,
>>>>
>>>> I have a stata file where I have formatted the date in a  MONTHLY
>>>> format ie  . format %tmMon_CCYY
>>>>
>>>> When I export this file to excel saying outsing using 10.xls SOME of
>>>> my dates get messed up. Has anyone else experienced this ?
>>>>
>>>>
>>> When you say "SOME" of your dates get messed up, what do you mean?
>>> Could you provide an example of how Excel is correctly interpreting
>>> dates and an example of some that are being mis-interpreted.  Otherwise
>>> people will only be able to guess if they have experienced what you are
>>> experiencing.
>>>
>>> I'd also avoid -outsheet-ing to a file with the extension ".xls" since
>>> this implies that it is an Excel formatted file, which is not the case
>>> since -outsheet- writes to ASCII text files, and by default uses <Tab>
>>> as a delimiter (although this can be modified using the -, comma- or -,
>>> delim("char")- options).  If you use the -,comma- option the giving your
>>> file the extensions ".csv" you will likely find that particular file
>>> extension is already associated with Excel, and double-clicking the file
>>> from Explorer will invoke Excel which will then attempt to import your
>>> data (assuming your on a windows OS with standard Excel installation).
>>>
>>> Neil
>>>
>>> --
>>> "Only two things are infinite, the universe and human stupidity, and I'm
>>> not sure about the former." - Albert Einstein
>>>
>>>
>>> ***********************************************************************
>>> This  message  may  contain  confidential and  privileged  information.
>>> If you  are not the  intended recipient  you should not  disclose, copy
>>> or distribute information in this e-mail or take any action in reliance
>>> on its contents.  To do so is strictly  prohibited and may be unlawful.
>>> Please  inform  the  sender that  this  message has  gone astray before
>>> deleting it.  Thank you.
>>>
>>> 2008 marks the 60th anniversary of the NHS.  It's an opportunity to pay
>>> tribute to the NHS staff and volunteers who help shape the service, and
>>> celebrate their achievements.
>>>
>>> If you work for the NHS  and  would like  an NHSmail  email account, go
>>> to: www.connectingforhealth.nhs.uk/nhsmail
>>> ***********************************************************************
>>>
>>> *
>>> *   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/
>>
> *
> *   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/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index