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?
  
1/(.375 - .333333)

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

library(chron)
dd <- c(t0 = "30.12.1899 00:00:00", t1 = "3.7.1981 09:00:00",

x <- chron(sub(" .*", "", dd), sub(".* ", "", dd), format = c("D.M.Y", "H:M:S"))

diff(x)

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

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