I have used xlsReadWrite to read data from an Excel spreadsheet. I had a problem with converting times of the day so that I could create POSIXct date-time objects. I was wondering if there was a better solution. Excel stores times of the day as fractions of a day so I wrote a function to convert the fraction to a number of seconds, extract the hours, minutes and seconds and output it in ISO standard format (%H:%M:%S). Given a date obtained from an Excel date using as.Date I could then make up my POSIXct date-time objects. This seems like it would be such a standard operation that there may be existing solutions that I have missed. Any comments, advice? David Scott _________________________________________________________________ David Scott Department of Statistics The University of Auckland, PB 92019 Auckland 1142, NEW ZEALAND Phone: +64 9 373 7599 ext 85055 Fax: +64 9 373 7018 Email: d.scott at auckland.ac.nz Graduate Officer, Department of Statistics Director of Consulting, Department of Statistics
The chron package stores times as a fraction of the day. See R News 4/1. On Sun, Feb 22, 2009 at 8:26 PM, David Scott <d.scott at auckland.ac.nz> wrote:> > I have used xlsReadWrite to read data from an Excel spreadsheet. > > I had a problem with converting times of the day so that I could create > POSIXct date-time objects. I was wondering if there was a better solution. > > Excel stores times of the day as fractions of a day so I wrote a function to > convert the fraction to a number of seconds, extract the hours, minutes and > seconds and output it in ISO standard format (%H:%M:%S). > > Given a date obtained from an Excel date using as.Date I could then make up > my POSIXct date-time objects. > > This seems like it would be such a standard operation that there may be > existing solutions that I have missed. > > Any comments, advice? > > David Scott > > _________________________________________________________________ > David Scott Department of Statistics > The University of Auckland, PB 92019 > Auckland 1142, NEW ZEALAND > Phone: +64 9 373 7599 ext 85055 Fax: +64 9 373 7018 > Email: d.scott at auckland.ac.nz > > Graduate Officer, Department of Statistics > Director of Consulting, Department of Statistics > > ______________________________________________ > 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. >
> Excel stores times of the day as fractions of a day so I wrote a function to > convert the fraction to a number of seconds, extract the hours, minutes and > seconds and output it in ISO standard format (%H:%M:%S).What about the 'colClasses' or 'dateTimeAs' argument in 'read.xls' which gives back an iso-date-time character string. Or you could use the 'dateTimeToStr' function to convert the date number to a character string formatted as indicated. As a question: would additional entries (e.g.) 'posixctdatetime' and/or 'chrondatetime' for the 'colClasses' argument be useful? -- Regards, Hans-Peter
RExcel (the addin package for RExcel I wrote) tries very hard to transfer dates and times from Excel to R. David Scott wrote:> > I have used xlsReadWrite to read data from an Excel spreadsheet. > > I had a problem with converting times of the day so that I could create > POSIXct date-time objects. I was wondering if there was a better solution. > > Excel stores times of the day as fractions of a day so I wrote a > function to convert the fraction to a number of seconds, extract the > hours, minutes and seconds and output it in ISO standard format (%H:%M:%S). > > Given a date obtained from an Excel date using as.Date I could then make > up my POSIXct date-time objects. > > This seems like it would be such a standard operation that there may be > existing solutions that I have missed. > > Any comments, advice? > > David Scott > > _________________________________________________________________ > David Scott Department of Statistics > The University of Auckland, PB 92019 > Auckland 1142, NEW ZEALAND > Phone: +64 9 373 7599 ext 85055 Fax: +64 9 373 7018 > Email: d.scott at auckland.ac.nz > > Graduate Officer, Department of Statistics > Director of Consulting, Department of Statistics > > ______________________________________________ > 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. > > > ------------------------------------------------------------------------ > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.0.237 / Virus Database: 270.11.3/1966 - Release Date: 02/22/09 17:21:00 >-- Erich Neuwirth, University of Vienna Faculty of Computer Science Computer Supported Didactics Working Group Visit our SunSITE at http://sunsite.univie.ac.at Phone: +43-1-4277-39464 Fax: +43-1-4277-39459