data difference in `as.POSIXct` with Excel

The problem is that either R of Excel is rounding the number to two decimals. When you convert the for example the cell with 8/8/2013 15:10 to text formatting (in Excel on Mac OSX), you get the number 41494.63194.

When you use:

as.POSIXct(41494.63194*86400, origin="1899-12-30",tz="GMT")

it will give you:

[1] "2013-08-08 15:09:59 GMT"

This is 1 second off from the original date (which is also an indication that 41494.63194 is rounded to five decimals).

Probably the best solution to do is export your excel-file to a .csv or a tab-separated .txt file and then read it into R. This gives me at least the correct dates:

> df
            datum
1  8/8/2013 15:10
2 7/26/2013 10:30
3 7/11/2013 14:20
4 3/28/2013 16:15
5 3/18/2013 15:50

Given

x <- c("8/8/2013 15:10","7/26/2013 10:30","7/11/2013 14:20","3/28/2013 16:15","3/18/2013 15:50")

(which is read as a character vector),

try

x <- as.POSIXct(x, format = "%m/%d/%Y %H:%M", tz = "GMT")

It reads correctly as a POSIXct vector to me.

Maybe it is a matter of how R reads the data. Just an example here with lubridate seems to work well.

x <- "8/8/2013 15:10"
library(lubridate)
dmy_hm(x, tz = "GMT")
[1] "2013-08-08 15:10:00 GMT"