[R] Serial Date

[Raoni Rosa Rodrigues]

Hello Mr. Allan and Mr. David!

Thanks very much for your time and lessons.

Works pretty well, Mr. Allan, specially after Mr. David explanation (and the 2 days-bug correction). 

Just a curious behavior of data tranformation: the R-transformation date was 2min and 20s late compared with excel 2007 time. Did you know why? 

Attached a file with more than 1800 date-time values, that I used to compare data. I ran this code on R

Data = function (x){
    c (ISOdatetime (1900, 1, 1, 0, 0, 0) + ((x-2)*60*60*24))
    }

and pasted result on excel where I compared with date time personalized format (YYYY/MM/DD HH:MM:SS) of excel of original file.

When I used a corrected code

Data = function (x){
    c (ISOdatetime (1900, 1, 1, 0, 2, 20) + ((x-2)*60*60*24))
    }

worked pretty well.

Thanks again!

All best,

Raoni

--- Em seg, 14/3/11, David Winsemius <dwinsemius at comcast.net> escreveu:

De: David Winsemius <dwinsemius at comcast.net>
Assunto: Re: [R] Serial Date
Para: "Allan Engelhardt" <allane at cybaea.com>
Cc: "Raoni Rosa Rodrigues" <raonirosa at yahoo.com.br>, "R Help" <r-help at r-project.org>
Data: Segunda-feira, 14 de Março de 2011, 13:00


On Mar 14, 2011, at 6:36 AM, Allan Engelhardt wrote:

> 
> 
> On 14/03/11 02:00, Raoni Rosa Rodrigues wrote:
>> Hello R Help,
>> 
>> I'm working in a project with a software that register date and time data in serial time format. This format is used by excel, for exemple. In this format, 40597.3911423958 is 2011/2/23 09:23:15.
> 
> Not on my system,

Because ... There is a reasonably well understood MS bug in how it handles the non-existent 1900-02-29. (It _still_ accepts that as a valid date and calls it 60. I just re-demonstrated this amazingly persistent bug behavior on the Mac Excel 2011 variant.  )

http://finzi.psych.upenn.edu/R/library/cxxPack/html/serialNumber.html
http://finzi.psych.upenn.edu/R/Rhelp02/archive/22942.html

(I don't think Gabor's speculation regarding the migration from Windows Excel to Mac Excel is possibly correct since Excel was introduced on the Mac years before Windows was even a crash-prone GUI on top of MS-DOS. The claim on one MS page is that the bug originated in Lotus 1-2-3 and was emulated by Multiplan for compatibility. )

--David.



> but this should get you going:
> 
> ( a<- as.POSIXct("2011-02-23 09:23:15.00001", tz="GMT") )
> # [1] "2011-02-23 09:23:15 GMT"
> ( b<- difftime(a, ISOdatetime(1900,1,1,0,0,0), tz="GMT", units="days") )
> # Time difference of 40595.39 days
> as.double(b)
> # [1] 40595.39
> options("digits.secs" = 6)
> ISOdatetime(1900, 1, 1, 0, 0, 0) + 40597.3911423958*60*60*24 # TZ dependent
>> [1] "2011-02-25 09:23:14.702997 GMT"
> 
> 
> 
> Hope this helps a little.
> 
> Allan
> 
>>  First part is number os days since 1900/1/1, and second part is a fraction of a day.
>> 
>> I need to make this transformation in R, and use it to make some algebrian operations. I found that function 'serialNumber' of package 'cxxPack' transform a as.data class object in this format, but without time fraction. But I can't find nothing to do the inverse way, transform serial date format in a POSIX class, for exemple.
>> 
>> Since now, thanks for your time and atention.
>> 
>> All best,
>> 
>> Raoni
>> Associate Researcher
>> Fish Passage Center
>> UFMG, Brazil.
>> 
>> 
>> "Fica proibido o uso da palavra liberdade,
>> 
>> a qual será suprimida dos dicionários
>> 
>> e do pântano enganoso das bocas.
>> 
>> A partir deste instante
>> 
>> a liberdade será algo vivo e transparente
>> 
>> como um fogo ou um rio,
>> 
>> e a sua morada será sempre
>> 
>> o coração do homem."
>> 
>> (Thiago de Mello)
>> 
>> 
>> 
>>     [[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]]
> 
> ______________________________________________
> 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, MD
West Hartford, CT


      
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: DateTime.txt
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20110314/7d81b690/attachment.txt>