CODEWITHSUNDEEP
rdate
Samima
Samima

Reputation: 73

Date column produces unknown numbers in r

I wrote a data frame in CSV format with R and opened it with Excel. Then I converted it to an Excel Workbook and made some edit on it. When I imported that Excel file in R again, the date column looked like this. (Few are in numbers and few are dates.)

Date
39387
39417
15/01/2007
16/01/2007

I tried to change the format with Excel but failed. General or number option in Excel format generate the number like I mentioned which is in no way related to the date.

Upvotes: 2

Views: 312

Answers (1)

pnuts
pnuts

Reputation: 59460

It seems all four of your example are in respect of dates in January (11th and 12th for the first two), that the Excel involved has been configured to expect MDY (rather than DMY as popular in UK for example) and its date system to ‘1900’, and that the CSV has written the dates as 'conventional' dates rather than date index numbers.

So what Excel saw first was:

11/01/2017
12/01/2017
15/01/2017
16/01/2017

intended to represent the 11th, 12th, 15th and 16th of January. However, because expecting MDY Excel has interpreted (coercing the Text to date index) the first two entries as November 1 and December 1. For ‘months’ 15 and 16 it did no interpretation and just reported the text as in the CSV.

The coercion is automatic with no option to turn it off (nor 'reversed' with a change of format). Various ways to address this (common) issue are mentioned in the link kindly provided by @Gerard Wilkinson. I am not providing a full solution here since (a) some things are under user control (eg the ‘1904’ system is an option, as is the choice whether MDY or DMY) and (b) the preferred route will depend to some extent on how often required and what the user is most familiar with.

Upvotes: 1

Related Questions