Date format issue while writing xlsx in R

Question

Exp is a data frame , as given below:

> Exp
  Branch       Date Division
1      2 2023-02-10      820
2      2 2023-02-10      280
3      2 2023-02-10      935
4      2 2023-02-10      359
5      2 2023-02-10      450
> str(Exp)
'data.frame':   5 obs. of  3 variables:
 $ Branch  : num  2 2 2 2 2
 $ Date    : Date, format: "2023-02-10" "2023-02-10" "2023-02-10" ...
 $ Division: num  820 280 935 359 450

When I try to export it to excel using

options(xlsx.date.format = "yyyy-mm-dd")
library(xlsx)
write.xlsx(Exp, file = "Exp.xlsx", sheetName = "Expfile")

the output is the following:

I want the date to be appear as 2023-02-10 as shown in the data frame while writing data in Excel and not as 02.10.2023, when it is opened. Something is going wrong, despite setting options and Date being in date format as you see. What is missing here?

Answer 1

In the package {xlsx}(Java dependent), the xlsx.date.format option need to be specified in Java date format, where mm means minutes and MM means months. So you should do

options(xlsx.date.format = "yyyy-MM-dd")

If that still doesn't work, you can turn to another package {openxlsx}.

library(openxlsx)
options(openxlsx.dateFormat = "yyyy-mm-dd")
write.xlsx(list(Expfile = Exp), file = "Exp.xlsx")

where Expfile is the sheet name.