2

What is the correct date format for the new writexl package? I tried it on lubridate dates, and the resulting Excel spreadsheet contains strings of the form yyyy-mm-dd (i.e. not Excel dates).

lebelinoz
  • 4,890
  • 10
  • 33
  • 56

2 Answers2

1

The purpose of the writexl package is to essentially create a mirror image of an R table in an excel file format. So having data in one of the usual R date/time formats like as.Date, as.POSIXct, etc. won't translate to a date format shifting from YYYY-mm-dd to d/m/y while being exported to excel. If you'd like it in a more standard excel date/time format in the excel file, it's best to convert it prior to exporting it with something like the strftime() function, like this:

require(writexl)

write_xlsx(
  data.frame(date=strftime(c("2017-09-10","2017-09-11","2017-09-12"),"%d/%m/%y")),
  "~/Downloads/mydata.xlsx")

Output (in xlsx file):

date
10/09/17
11/09/17
12/09/17

Edit:

If you'd like the data to be an Excel date format once it's in the new file, then adding as.POSIXct() to the above will ensure that.

www
  • 4,124
  • 1
  • 11
  • 22
  • You mean something like `format( ... , "%d/%m/%y)`? I've tried a few, as per your suggestion, and they don't quite work. Always strings. – lebelinoz Sep 11 '17 at 04:41
  • @lebelinoz - If it's a character string, it will create that mirror image in the excel file either way. If you're looking for a way to convert YYYY-mm-dd to d/m/y before creating the file though, I've included a way above with the strftime() function. – www Sep 11 '17 at 04:56
  • I'm looking for a way to create dates in Excel, not strings which look like dates. Failing to export dates as actual dates into Excel is a great source of frustration for data analysts who work with Office products. Fortunately, I think I've found a solution using `as.POSIXct` (see my answer) – lebelinoz Sep 11 '17 at 04:59
  • (I gave +1 to your answer, though, because you mentioned it and I tried it and it worked :) ) – lebelinoz Sep 11 '17 at 05:00
  • 1
    @lebelinoz - I see. I thought you were looking for a way to have it automatically shift the format, which I don't think as.POSIXct will do. But if you just want it stored as an Excel date class, then yes, that will work. I'll add an edit to my answer with that new information. – www Sep 11 '17 at 05:02
0

It worked when I converted my dates to POSIXct dates using as.POSIXct.

lebelinoz
  • 4,890
  • 10
  • 33
  • 56