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?

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
– Ray, Commented Mar 14, 2023 at 14:01
Hey Darren, It worked wonderful. Thanks a lot. Could you please pose this as an answer to upvote it? — Ray
– Ray, Commented Mar 17, 2023 at 7:01

Darren Tsai · Accepted Answer · 2023-03-17 07:56:43Z

4

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.

answered Mar 17, 2023 at 7:56

Darren Tsai

36.6k6 gold badges27 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Date format issue while writing xlsx in R

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related