converting Excel-date string to date and time

Question

I have imported some data from Excel containing a date and time cell. When I try to convert the data into a date and time format I only get very strange results. If I go for only date there is no problem and I get the correct date. (I am working in MacOS)

date <- 40259.93645833333

as.POSIXlt(date,origin='1904-01-01 00:00:00')
"1904-01-01 12:10:59 CET"
as.POSIXlt(date,origin='1970-01-01 00:00:00')
"1970-01-01 12:10:59 CET"
as.POSIXlt(date,origin='2010-01-01 00:00:00')
2010-01-01 12:10:59 CET"
as.Date(date,origin='1904-01-01')
"2014-03-23" #Correct date

I would like to know how to proceed and keep the time as well.

Did you tried XLConnect? It should import data correctly (also with dates). — Maciej
– Maciej, Commented Mar 18, 2014 at 8:16

Roland · Accepted Answer · 2014-03-18 08:26:59Z

5

The number passed to as.POSIXlt are seconds since the origin:

as.POSIXlt(date*24*3600,origin='1904-01-01 00:00:00')
[1] "2014-03-23 23:28:29 CET"

Also, generic advice recommends using POSIXct instead of POSIXlt.

answered Mar 18, 2014 at 8:26

Roland

134k12 gold badges203 silver badges305 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

converting Excel-date string to date and time

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related