Write dates to Excel properly from R

Question

When I write dataframe to the file it considers all columns as characters including the date column.

options(xlsx.date.format = "yyyy-mm-dd")
write.xlsx(data, excel_filename, sheetName = "Data")

How can I write data to xlsx file such that when I work with this column it considered as date by default?

Solution: Turns the class of the column was character. After conversion with as.Date everything is saved properly.

Answer 1

The reference manual for xlsx explains it very clearly with example. Below is a slight modified Source: https://cran.r-project.org/web/packages/xlsx/xlsx.pdf I think, you were following the same approach. working with workbook saves the format of the date.

wb <- createWorkbook(type="xlsx")
 sheet <- createSheet(wb, sheetName="addDataFrame1")
 data <- data.frame(date=seq(as.Date("1999-01-01"), by="1 year", length.out=10))
 addDataFrame(data, sheet, startRow = 1, startColumn=1)
 # to change the default date format use something like this
 options(xlsx.date.format="dd MMM, yyyy")
 # Don't forget to save the workbook ...
 saveWorkbook(wb, "Path/test.xlsx") # your path to the excel sheet

Answer 2

You may try using lubridate or chron libraries for this task. However, I do not think your issue is coming from R but more with how excel is reading it. In your question, is the image you show of how you want it to look or how it currently looks? In any case, when using chron for example, you can say

 format.Date(dates, "%Y/%m/%d")