Output dates formatted mm-dd-yyyy when monthly data used

[RosalynLP]

Normally when you save out dates using writeData() they appear in Excel in the format dd-mm-yyyy for me but I have some data which are monthly, i.e. with dates just on the first of each month. In that case they appear in the format mm-dd-yyyy and there doesn't seem to be an option to override this. I realise this might be intentional behaviour but would like to get round it if possible and just have dd-mm-yyyy.

Example

dates <- seq(as.Date("2022-02-01"), length = 6, by = "month")
numbers <- seq(1, length=6)

df <- data.frame(dates, numbers)

wb <- openxlsx::createWorkbook()
addWorksheet(wb, sheetName="test")
writeData(wb, df, sheet="test")

saveWorkbook(wb, "test.xlsx", overwrite=TRUE)

Gives outputs in mm-dd-yyyy format when Excel opened.

Would you please be able to advise how I can avoid this? Thanks!

[JanMarvin]

Hi @RosalynLP ,

you're looking for the "openxlsx.dateFormat". Please see the code example below. You can replace the date format with some other open xml date format.

library(openxlsx)

options("openxlsx.dateFormat" = "ddMMMyyyy") # 01Jan2022, 01Feb2022 ...
dates <- seq(as.Date("2022-02-01"), length = 6, by = "month")
numbers <- seq(1, length = 6)

df <- data.frame(dates, numbers)

wb <- createWorkbook()
addWorksheet(wb, sheetName="test")
writeData(wb, df, sheet="test")
openXL(wb)

I close this issue. Feel free to reopen if it does not work as expected.

[RosalynLP]

Hi @JanMarvin thank you, amazing :)