Time only POSIXct columns end up a day out

[sch56]

Excel has a legacy of considering 29-Feb-1900 as a leap year day, when it didn't exist.
When I read a column with date/time format and value 0.25 (which appears in Excel time format as 6:00:00 AM) with readxl it creates a POSIXct column.
$ CPUTM_MKPF : POSIXct[1:336433], format: "1899-12-31 06:00:00"
When I write this back out to excel using writexl it is stored as "1900-01-01 06:00:00 UTC" which has the numeric value 1.25.

This can be terribly misleading.

I am not sure if this issue is platform dependent, but readxl appears to have addressed the anomaly. It would be great if writexl was consistent with this.

[jmcnamara]

Libxlsxwriter takes the infamous Excel 1900 leap year into account but writxl does a direct date conversion (for performance I guess) and get bitten by it for dates before 1900-03-01.

I'll submit a fix.

[jmcnamara]

I've pushed a fix in #59.

Here is a small working example:

> df <- data.frame(dates   = c(as.POSIXct("1899-12-31 00:00", 'UTC'),
+                              as.POSIXct("1900-01-01 00:00", 'UTC'),
+                              as.POSIXct("1900-02-28 00:00", 'UTC'),
+                              as.POSIXct("1900-03-01 00:00", 'UTC')),
+                  numbers = c(0, 1, 59, 61),
+                  check   = xl_formula(c("=A2-B2", "=A3-B3", "=A4-B4", "=A5-B5")))
> 
> writexl::write_xlsx(df, 'datetimes.xlsx')
> 
> readxl::read_xlsx('datetimes.xlsx')
# A tibble: 4 x 3
  dates               numbers check
  <dttm>                <dbl> <dbl>
1 1899-12-31 00:00:00       0     0
2 1900-01-01 00:00:00       1     0
3 1900-02-28 00:00:00      59     0
4 1900-03-01 00:00:00      61     0

Output:

Without the fix you get this error from read_xlsx:

> readxl::read_xlsx('datetimes.xlsx')
# A tibble: 4 x 3
  dates               numbers check
  <dttm>                <dbl> <dbl>
1 1900-01-01 00:00:00       0     0
2 1900-01-02 00:00:00       1     0
3 NA                       59     0
4 1900-03-01 00:00:00      61     0
Warning message:
In read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet,  :
  NA inserted for impossible 1900-02-29 datetime

[sch56]

Great response! Will definitely help when working with data out of SAP! They tend to separate date and time into separate columns, so great to end up on the right day when they are re-combined.