0

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: enter image description here

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?

Ray
  • 321
  • 2
  • 12
  • I tried with options(xlsx.date.format = "yyyy-MM-dd") and it did't help. The output has 02.10.2023 as usual, unfortunately. – Ray Mar 14 '23 at 14:01
  • 1
    Hey Darren, It worked wonderful. Thanks a lot. Could you please pose this as an answer to upvote it? – Ray Mar 17 '23 at 07:01
  • Hi, if my answer has solved your question, you could consider accepting it by clicking the check mark. Thanks! – Darren Tsai Mar 30 '23 at 06:22

1 Answers1

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.

Darren Tsai
  • 32,117
  • 5
  • 21
  • 51