0

I have an Excel file that have dates/times as part of the data. This data in the Excel file looks like the following:

4/28/2016  11:30:00 AM

When I import this file into R using xlsx or readxl libraries the dates that I get in R are missing one second. For example, the previous date becomes 4/28/2016 11:29:59 AM.

rjss
  • 935
  • 10
  • 23
  • Are all of the dates off by 1 second or are just some? – John Coleman Jun 24 '16 at 04:27
  • Yes all dates seem to be off by one second. – rjss Jun 24 '16 at 04:28
  • 1
    You need to provide a reproducible example for this type of question. http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example – Hack-R Jun 24 '16 at 04:30
  • 1
    Maybe the packages that you are using use a date-conversion algorithm which is somehow missing the most recent leap second: https://en.wikipedia.org/wiki/Leap_second . If that is the case, perhaps updating the packages could help. – John Coleman Jun 24 '16 at 11:36
  • I am not sure how to share an Excel file but I was wrong. The problem seem to be related to read_excel (readxl package). If you copy and paste `4/28/2016 11:30:00 AM` to Excel (cell A1) and run the following: > read_excel("test.xlsx",col_names = FALSE) you should see the problem. Since the timezone assumed by read_excel is UTC it seems it could be the problem suggested by @JohnColeman – rjss Jun 25 '16 at 03:49
  • How does the conversion handle `4/28/2015/ 11:30:00 AM` -- which was before the most recent leap second? If the error doesn't occur there -- then the leap second is to blame. If it still occurs, my guess is that a fractional number of seconds is being truncated down rather than rounded up. Without looking at the source, this is just a stab in the dark. – John Coleman Jun 25 '16 at 12:31
  • @JohnColeman I tested readxl with `4/28/2015 11:30:00 AM` and got the same answer (missing one second) so I either using the package incorrectly or maybe is a bug. Will send a message to the author to confirm and will update this post if I get additional info. – rjss Jul 01 '16 at 02:46
  • Odd. Maybe somewhere in the code time in seconds is being read into a `long` rather than a `double` and a fraction of a second is being lost in the coercion. – John Coleman Jul 01 '16 at 02:57

0 Answers0