I'm working on an R project where one aim is to create Excel Sheets. The Problem ist that some Date values (old ones) have a shift of one day, but others are working nicely!
The shown code creates a data.frame with one colm called Dates. In this column the values 01.01.1900, 01.01.1950 and 01.01.2000 are stored. After creating an Excel Sheet the 01.01.1900 is changed to 02.01.1900, whereby the others stay the same.
dates = as.Date(c("01.01.1900","01.01.1950","01.01.2000"), "%d.%m.%Y")
df = data.frame(Dates=dates)
wb <- createWorkbook()
addWorksheet(wb, sheetName = "Dates")
writeData(wb, 1, df)
saveWorkbook(wb, "dates.xlsx", overwrite = TRUE)
It would be great if someone could explain me why this happened, and even better provide a solution for it.
All the best, Andy
It is known as the leap-year bug of Excel. It incorrectly assumes that 29/02/1900 was an actual day. However, it was implemented like this on purpose to ensure compatibility with Lotus 1-2-3 spreadsheets.
As a result, you will see that any dates between 01/01/1900 and 28/02/1900 will have this issue. When writing to a spreadsheet with R it will shift the date forward by 1 day, and when reading from a spreadsheet it should shift it backward by 1 day.
There is an open issue on openxlsx's github for this - so it is possible that this might be fixed in the future. See: openxlsx github issue
Generally speaking, I think it should be rare that you need to write dates in this date-range. Except off course as a placeholder for a missing date.
If you need a workaround though, you could always write a small function to manually add 1 day to the affected dates before writing to excel. For example:
library(tidyverse)
library(lubridate)
library(openxlsx)
# Helper Function
fix_dates_for_excel_write <- function(df) {
df %>%
as_tibble() %>%
mutate_if(is.Date, ~ if_else(. <= ymd(19000228), . - days(1), .))
}
# Affected dates to test
dates = as.Date(c("01.01.1900", "02.01.1900",
"27.02.1900", "28.02.1900",
"01.03.1900", "01.01.1950"), "%d.%m.%Y")
df = data.frame(Dates=dates)
# Apply helper function
df <- fix_dates_for_excel_write(df)
# Write to Excel
writeData(wb, 1, df)