 |
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: Date format export to excel
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 <[email protected]> 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/