Hi All!
This seems to be trival but I am not able to find a solution for it.
I have a dataframe with datetime columns in form of ("%d/%m/%y
%H:%M:%OS").
I write it to csv file. Whne i open the csv file the date time format are
in some number form .
So even if I use custome settings from excel to change it into date time
format, it gives me wrong value.
My data frame is as below:
PostDate Status ArrTime
NumGuests
2014-08-14 16:13:08.850 O 2012-01-13 00:00:00.000
6
2014-08-14 16:13:08.850 A
-SB
[[alternative HTML version deleted]]
Maybe converting POSIXct to character string using "format" before writing to csv will help. On 20 August 2014 17:23, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:> Hi All! > > This seems to be trival but I am not able to find a solution for it. > I have a dataframe with datetime columns in form of ("%d/%m/%y > %H:%M:%OS"). > > I write it to csv file. Whne i open the csv file the date time format are > in some number form . > So even if I use custome settings from excel to change it into date time > format, it gives me wrong value. > > My data frame is as below: > > PostDate Status ArrTime > NumGuests > 2014-08-14 16:13:08.850 O 2012-01-13 00:00:00.000 > 6 > 2014-08-14 16:13:08.850 A > > > -SB > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >[[alternative HTML version deleted]]
Tried that..does not help :( On Wed, Aug 20, 2014 at 8:03 AM, Saurabh Agrawal <sagrawal at idrcglobal.com> wrote:> Maybe converting POSIXct to character string using "format" before writing > to csv will help. > > > > On 20 August 2014 17:23, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote: > >> Hi All! >> >> This seems to be trival but I am not able to find a solution for it. >> I have a dataframe with datetime columns in form of ("%d/%m/%y >> %H:%M:%OS"). >> >> I write it to csv file. Whne i open the csv file the date time format are >> in some number form . >> So even if I use custome settings from excel to change it into date time >> format, it gives me wrong value. >> >> My data frame is as below: >> >> PostDate Status ArrTime >> NumGuests >> 2014-08-14 16:13:08.850 O 2012-01-13 >> 00:00:00.000 >> 6 >> 2014-08-14 16:13:08.850 A >> >> >> -SB >> >> [[alternative HTML version deleted]] >> >> ______________________________________________ >> R-help at r-project.org mailing list >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide >> http://www.R-project.org/posting-guide.html >> and provide commented, minimal, self-contained, reproducible code. >> > >-- Sneha Bishnoi +14047235469 H. Milton Stewart School of Industrial & Systems Engineering Georgia Tech [[alternative HTML version deleted]]
This problem is in Excel or your use thereof, not in R, and is therefore not
technically "on topic" here.
FWIW I am aware that localization of Excel can change the default date formats
for input. I suspect that your installation of Excel has a different default
date format than you are using in R (like MDY) that is attempting to convert the
file before you start messing with formats. This would incorrectly interpret
some cells and fail entirely for others (leaving those cells as strings). My
suggestion is to have R output MDY rather than DMY. If that is not satisfactory
then you probably ought to ask for help in an Excel forum.
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live
Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.
On August 20, 2014 4:53:41 AM PDT, Sneha Bishnoi <sneha.bishnoi at
gmail.com> wrote:>Hi All!
>
>This seems to be trival but I am not able to find a solution for it.
>I have a dataframe with datetime columns in form of ("%d/%m/%y
>%H:%M:%OS").
>
>I write it to csv file. Whne i open the csv file the date time format
>are
>in some number form .
>So even if I use custome settings from excel to change it into date
>time
>format, it gives me wrong value.
>
>My data frame is as below:
>
> PostDate Status ArrTime
> NumGuests
>2014-08-14 16:13:08.850 O 2012-01-13
>00:00:00.000
> 6
> 2014-08-14 16:13:08.850 A
>
>
>-SB
>
> [[alternative HTML version deleted]]
>
>______________________________________________
>R-help at r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.
On Aug 20, 2014, at 4:53 AM, Sneha Bishnoi wrote:> Hi All! > > This seems to be trival but I am not able to find a solution for it. > I have a dataframe with datetime columns in form of ("%d/%m/%y %H:%M:%OS"). > > I write it to csv file. Whne i open the csv file the date time format are > in some number form .What does "same number form" mean?> So even if I use custome settings from excel to change it into date time > format, it gives me wrong value. > > My data frame is as below: >PostDate Status ArrTime NumGuests 2014-08-14 16:13:08.850 O 2012-01-13 00:00:00.000 6 2014-08-14 16:13:08.850 A> > -SB > > [[alternative HTML version deleted]]The reason you are asked to post in plain text is to avoid the line wrapping and other mangling of data that html formatting causes. I've reformatted your posting to be what appears to be a very incomplete representation of your file. I substituted tabs for the varying number of space -- opened and empty excel workbook -- formatted the first and third columns with a custom format for a date-time in the POSIX standard format (or a close as I can get to that in Excel, anyway) as illustrated in the attached .png file. -- open the tab-separated file. Dates and times all agree.> R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.David Winsemius Alameda, CA, USA