0

I've created a dataframe in R and one of my columns convert a date such as 01/08/2018 (dd/mm/yyyy) into text form Aug-18 (mmm-yy). However, when I write this to csv using the write.csv function, Excel automatically converts this to date.

Is there a way I can specify the column type to be "Text" so that Excel doesn't change it to date format?

Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
CodeMeARiver
  • 79
  • 1
  • 8
  • 1
    why don't you write into `xlsx` right away? google: `r openxlsx` – Andre Elrico Oct 16 '18 at 13:42
  • @Jaap I was about to write the same. Excel estimates the "right" types from CSV. Use `openxlsx` to have control over stuff! – Andre Elrico Oct 16 '18 at 13:47
  • Excel's formatting as a date, text, etc. is up to Excel, not R. There's no formatting or data types in a CSV file--it's just text – camille Oct 16 '18 at 13:48
  • 1
    @AndreElrico I mostly combine `writeData` and `addStyle` from `openxlsx`, which gives you quite some control over how stuff is displayed in Excel. – Jaap Oct 16 '18 at 13:53
  • https://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates/6023847 – G. Grothendieck Oct 16 '18 at 13:54

1 Answers1

0

One simple trick that IMHO gets far too little attention is to pad your date colums with whitespace, e.g. df$mydate <- paste(' ', df$mydate, sep=''). This stops Excel from translating the text as dates.

I have started routinely doing that for all kinds of risky columns when doing R<->Excel transformations.

Taken from here: https://support.office.com/en-us/article/stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8

Otto Kässi
  • 2,943
  • 1
  • 10
  • 27