CODEWITHSUNDEEP
rexcelas.date
hagewhy
hagewhy

Reputation: 79

Why applying as.Date() on "general" formatted excel variables can't work?

I was reading in a .csv file into R, where the .csv file was originally saved in Excel format.

While preparing for the .csv file, i did a copy and paste of the content on a new spreadsheet, and the Date.Sold variable was formatted as "general" in Excel.

I am curious as to why applying the command as.Date() on "general" formatted date values (from Excel) can't work in R? It gives me a series of NA values. If I had formatted the Date.Sold variable into "date" in Excel before reading into R, the as.Date() command works fine. Why is this so? Is there any way to read these "general" formatted variables into proper dates in R?

Some information about my variable and the command I used:

 Str(Date.Sold):  $ Date.Sold : Factor w/ 789 levels "-","40965.00",..: 461 463 456 17

Command that I used:

A$Date = as.Date(A$Date.Sold, "%d/%m/%Y")

Upvotes: 1

Views: 69

Answers (2)

maRtin
maRtin

Reputation: 6516

I think you might misunderstand the as.Date() function: You always have to specify in which format the date you want to convert currently is.

For example, if your date in character representation looks like this "02/08/2014" then you need to convert it with format="%d/%m/%Y": 

as.Date("02/08/2014",format="%d/%m/%Y")

If your date is in the general excel format, you need to use the origin argument. For example:

as.Date(30829, origin = "1899-12-30")

In your case, you might need to do a type conversion first, since you have factors:

as.Date(as.integer(as.character(30829)), origin = "1899-12-30")

Upvotes: 1

csanjose
csanjose

Reputation: 164

You must give the year of the origin of dates in Excel: "1899-12-30" so your function should look like as.Date(date, origin = "1899-12-30").

Best regards,

Upvotes: 0

Related Questions