Write an R data frame to an Excel file maintaining Excel's default date format

Question

If you open up a new instance of Excel 2016 and enter a date in the following format 8/15 it will output in Excel like this:

Excel's format menu identifies this as Custom > d-mmm. Now let's move on to the R part of this question. I'll create a simple data frame:

df <- data.frame(col1 = as.Date(c("2021-01-01", "2021-02-15")), col2 = c(3, 9))
#>         col1 col2
#> 1 2021-01-01    3
#> 2 2021-02-15    9

I want to write this data frame to an Excel file, and default to Excel's Custom > d-mmm date format (as shown above).

My first attempt is quite generic, and uses the writexl package:

library(writexl)
write_xlsx(df, "df.xlsx")

Excel ends up reading these dates as Excel's yyyy-mm-dd format, not the Custom > d-mmm format I want. Let me get a little more sophisticated and try and use the openxlsx package to get what I want:

library(openxlsx)
options(xlsx.date.format = "d-mmm")
write.xlsx(df, file = "df.xlsx", asTable = TRUE)

This time, the output is in the Excel format Date > *3/14/2012. How can I get the date to be recognized in Excel, in the Excel Custom > d-mmm format? The output would look like this, and should not involve any manual Excel steps:

Answer 1

Apply the global formatting syntax as per the documentation

options(openxlsx.dateFormat = "d-mmm")

---

Documentation:

https://rdrr.io/cran/openxlsx/man/openxlsx.html