# Re: [R] How to convert Datetime numbers from Excel to POSIXt objects

From: Gabor Grothendieck <ggrothendieck_at_gmail.com>
Date: Fri, 14 Dec 2007 12:12:46 -0500

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