Peter Dalgaard
2007-Dec-14 16:23 UTC
[R] How to convert Datetime numbers from Excel to POSIXt objects
Rolf Fankhauser wrote:> Hi all, > > I need to compare time series data files of different time formats. I > had no problems with text format using strptime. > But how can I convert datetime numbers from Excel (days since 30.12.1899 > 00:00:00) into POSIXt objects? > For example 29770.375 should be converted to "03.07.1981 09:00:00" > > I tried the following code and encountered strange results: > > t1-t0 gives 29770.33 (should be 29770.375 in my opinion) > t1-t2 and t1-t3 are ok > t1-t4 gives 183.3333 (should be 183.375) > Are these rounding errors? > > 1/(.375 - .333333)[1] 23.99981 So your expectation is off by 1/24th of a day. Can you think of something that might affect time differences by that amount, depending on which times of the year you are comparing?> The R-code: > > t1 <- strptime("3.7.1981 09:00:00","%d.%m.%Y %H:%M:%S") > t0 <- strptime("30.12.1899 00:00:00","%d.%m.%Y %H:%M:%S") > t2 <- strptime("3.7.1981 00:00:00","%d.%m.%Y %H:%M:%S") > t3 <- strptime("1.7.1981 00:00:00","%d.%m.%Y %H:%M:%S") > t4 <- strptime("1.1.1981 00:00:00","%d.%m.%Y %H:%M:%S") > t1 - t0 > t1 - t2 > difftime(t1,t2,units="days") > t1 - t3 > t1 - t4 > > Thanks for any help or clarifications > > Rolf > > ______________________________________________ > 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. >-- O__ ---- Peter Dalgaard ?ster Farimagsgade 5, Entr.B c/ /'_ --- Dept. of Biostatistics PO Box 2099, 1014 Cph. K (*) \(*) -- University of Copenhagen Denmark Ph: (+45) 35327918 ~~~~~~~~~~ - (p.dalgaard at biostat.ku.dk) FAX: (+45) 35327907
Rolf Fankhauser
2007-Dec-14 17:06 UTC
[R] How to convert Datetime numbers from Excel to POSIXt objects
Hi all,
I need to compare time series data files of different time formats. I
had no problems with text format using strptime.
But how can I convert datetime numbers from Excel (days since 30.12.1899
00:00:00) into POSIXt objects?
For example 29770.375 should be converted to "03.07.1981 09:00:00"
I tried the following code and encountered strange results:
t1-t0 gives 29770.33 (should be 29770.375 in my opinion)
t1-t2 and t1-t3 are ok
t1-t4 gives 183.3333 (should be 183.375)
Are these rounding errors?
The R-code:
t1 <- strptime("3.7.1981 09:00:00","%d.%m.%Y %H:%M:%S")
t0 <- strptime("30.12.1899 00:00:00","%d.%m.%Y %H:%M:%S")
t2 <- strptime("3.7.1981 00:00:00","%d.%m.%Y %H:%M:%S")
t3 <- strptime("1.7.1981 00:00:00","%d.%m.%Y %H:%M:%S")
t4 <- strptime("1.1.1981 00:00:00","%d.%m.%Y %H:%M:%S")
t1 - t0
t1 - t2
difftime(t1,t2,units="days")
t1 - t3
t1 - t4
Thanks for any help or clarifications
Rolf
Gabor Grothendieck
2007-Dec-14 17:12 UTC
[R] How to convert Datetime numbers from Excel to POSIXt objects
Since you are getting the date times from Excel clearly you don't need time zones, etc. so you can use chron. See R News 4/1 for more.> library(chron) > dd <- c(t0 = "30.12.1899 00:00:00", t1 = "3.7.1981 09:00:00",+ t2 = "3.7.1981 00:00:00", t3 = "1.7.1981 00:00:00", t4 = "1.1.1981 00:00:00")> > x <- chron(sub(" .*", "", dd), sub(".* ", "", dd), format = c("D.M.Y", "H:M:S")) > > diff(x)Time in days: t1 t1 t2 t2 t3 t3 t4 t4 29770.375 -0.375 -2.000 -181.000 On Dec 14, 2007 12:06 PM, Rolf Fankhauser <rolf.fankhauser at gepdata.ch> wrote:> Hi all, > > I need to compare time series data files of different time formats. I > had no problems with text format using strptime. > But how can I convert datetime numbers from Excel (days since 30.12.1899 > 00:00:00) into POSIXt objects? > For example 29770.375 should be converted to "03.07.1981 09:00:00" > > I tried the following code and encountered strange results: > > t1-t0 gives 29770.33 (should be 29770.375 in my opinion) > t1-t2 and t1-t3 are ok > t1-t4 gives 183.3333 (should be 183.375) > Are these rounding errors? > > The R-code: > > t1 <- strptime("3.7.1981 09:00:00","%d.%m.%Y %H:%M:%S") > t0 <- strptime("30.12.1899 00:00:00","%d.%m.%Y %H:%M:%S") > t2 <- strptime("3.7.1981 00:00:00","%d.%m.%Y %H:%M:%S") > t3 <- strptime("1.7.1981 00:00:00","%d.%m.%Y %H:%M:%S") > t4 <- strptime("1.1.1981 00:00:00","%d.%m.%Y %H:%M:%S") > t1 - t0 > t1 - t2 > difftime(t1,t2,units="days") > t1 - t3 > t1 - t4 > > Thanks for any help or clarifications > > Rolf > > ______________________________________________ > 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. >
Rolf Fankhauser
2007-Dec-15 07:22 UTC
[R] How to convert Datetime numbers from Excel to POSIXt objects
Ok, I see, the difference comes from summer and winter time. Thanks for the hint! Stupid not to bear that in mind!! Peter Dalgaard wrote:>Rolf Fankhauser wrote: > > >>Hi all, >> >>I need to compare time series data files of different time formats. I >>had no problems with text format using strptime. >>But how can I convert datetime numbers from Excel (days since 30.12.1899 >>00:00:00) into POSIXt objects? >>For example 29770.375 should be converted to "03.07.1981 09:00:00" >> >>I tried the following code and encountered strange results: >> >>t1-t0 gives 29770.33 (should be 29770.375 in my opinion) >>t1-t2 and t1-t3 are ok >>t1-t4 gives 183.3333 (should be 183.375) >>Are these rounding errors? >> >>1/(.375 - .333333) >> >> >[1] 23.99981 > >So your expectation is off by 1/24th of a day. > >Can you think of something that might affect time differences by that >amount, depending on which times of the year you are comparing? > > > >>The R-code: >> >>t1 <- strptime("3.7.1981 09:00:00","%d.%m.%Y %H:%M:%S") >>t0 <- strptime("30.12.1899 00:00:00","%d.%m.%Y %H:%M:%S") >>t2 <- strptime("3.7.1981 00:00:00","%d.%m.%Y %H:%M:%S") >>t3 <- strptime("1.7.1981 00:00:00","%d.%m.%Y %H:%M:%S") >>t4 <- strptime("1.1.1981 00:00:00","%d.%m.%Y %H:%M:%S") >>t1 - t0 >>t1 - t2 >>difftime(t1,t2,units="days") >>t1 - t3 >>t1 - t4 >> >>Thanks for any help or clarifications >> >>Rolf >> >>______________________________________________ >>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. >> >> >> > > > >