5

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")

enter image description here

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.

Sergey Ivanov
  • 3,719
  • 7
  • 34
  • 59
  • Should it instead be [`options(openxlsx.dateFormat = "yyyy-mm-dd")`](https://cran.r-project.org/web/packages/openxlsx/vignettes/formatting.pdf)? – r2evans Aug 15 '16 at 18:30
  • 1
    http://stackoverflow.com/questions/23295571/how-to-prevent-write-csv-from-changing-posixct-dates-and-times-class-back-to-ch , does this help ? – user5249203 Aug 15 '16 at 18:37
  • @r2evans didn't work :( – Sergey Ivanov Aug 15 '16 at 18:44
  • @Sathish, Tried that too, but didn't work – Sergey Ivanov Aug 15 '16 at 18:44
  • @user5249203 do you mean it's possible to force excel to read it as date? – Sergey Ivanov Aug 15 '16 at 18:44
  • Were you able to follow the vignette pointed to in my last comment? It talks specifically about transferring dates to excel (pages 3-4). – r2evans Aug 15 '16 at 18:45
  • 1
    @SergeyIvanov : `write.xlsx(data.frame(seq(Sys.Date(), Sys.Date() + 2, "days")), "myfile.xlsx", sheetName = "Data", row.names = FALSE, col.names = FALSE)` – Sathish Aug 15 '16 at 18:45
  • @r2evans I tried it, but it's still the same – Sergey Ivanov Aug 15 '16 at 18:49
  • 1
    @Sathish's comment worked for me, inserting a field that Excel sees as "Date" format. Are you certain the values in `data` are actually `POSIX*` and not `character`? – r2evans Aug 15 '16 at 18:51
  • @Sathish, I'm not sure if I was explicit enough, but what I need is excel considering the column as date such that I can sort from oldest to newest, not from A to Z. I don't try the string to be in a format "YYYY-MM-DD", I already have it. – Sergey Ivanov Aug 15 '16 at 18:52
  • 1
    check if your date column in R dataframe has `class = "Date"` in it. For example, `class(Sys.Date())` – Sathish Aug 15 '16 at 18:52
  • Sorry for any confusion, I was looking at `openxlsx` (happened to be loaded for my current work). – r2evans Aug 15 '16 at 18:55
  • Thanks everyone, turns out in `data` indeed the dates were having class character (didn't know it, since I was getting from the database query). After conversion to as.Date(), everything works properly. Kudos to everyone! – Sergey Ivanov Aug 15 '16 at 19:07

2 Answers2

3

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")
user5249203
  • 4,436
  • 1
  • 19
  • 45
user5727
  • 61
  • 1
  • 13
3

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
user5249203
  • 4,436
  • 1
  • 19
  • 45