Excel has a legacy of considering 29-Feb-1900 as a leap year day, when it didn't exist

I've pushed a fix in <a class="issue-link js-issue-link" data-error-text="Failed to lo

Time only POSIXct columns end up a day out about writexl HOT 3 OPEN

sch56 commented on July 22, 2024

Time only POSIXct columns end up a day out

from writexl.

Comments (3)

jmcnamara commented on July 22, 2024 1

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.

from writexl.

jmcnamara commented on July 22, 2024

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

from writexl.

sch56 commented on July 22, 2024

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.

from writexl.

Recommend Projects