CODEWITHSUNDEEP
rexceldate
Franchise
Franchise

Reputation: 1261

Reformat Excel numeric date to R date

Looking to reformat a column pulled from Excel into a dataframe that contains the numeric Excel format (e.g. 40182).

as.date(40182, origin = "1899-12-30", format = '%b-%Y')

Returns:

[1] 5Jan2070

I'm looking for more along the lines of Jan-14 (short month, short year).

Being fairly new to R, I have been unable to determine what the cause of this is. Switching the origin date doesn't seem to change anything either. Any help?

Upvotes: 3

Views: 6348

Answers (2)

COLO
COLO

Reputation: 1114

In excel, number 40182 gives the date 04-01-2010 when formated as date. So I think you are looking for this:

format(as.Date(40182, origin = "1899-12-30"), '%b-%Y')

which gives:

[1] "Jan-2010"

hope it helps!!!

Upvotes: 3

Michael Vine
Michael Vine

Reputation: 335

##simulated excel. I used an excel to tes tthe code though. 
a<-seq(from = as.Date(Sys.Date()), to = seq(as.Date(Sys.Date()), 
length.out = 12, by= "-1 years")[2], by = "-1 days")
a<-as.numeric(a)
a<-a+25569
a<-as.Date(as.numeric(a[1:length(a)]), origin = as.Date("1970-01-01"))

##a is in One Year of dates like your excel list. 

a<-as.numeric(a)
a<-a-25569
a<-as.Date(as.numeric(a[1:length(a)]), origin = as.Date("1970-01-01"))
a<-format(a, "%b-%y")

hope this helps you.

Upvotes: 1

Related Questions