3

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?

Franchise
  • 1,081
  • 3
  • 15
  • 30
  • 1
    `as.Date(40182 - 25569, origin = "1970-01-01")`? – nghauran Feb 20 '18 at 20:33
  • > as.date(40182 - 25569, origin = "1970-01-01", format = '%b-%y') [1] 4Jan2000 – Franchise Feb 20 '18 at 20:34
  • 1
    There's the output of your code. 2 things: 1) this is an column of dates so I was hoping to have this change reflected over the enitrety & 2) the expected output of 41640 (the first number in the column) should be January of 2014. When I input that to as.date, it returns 2Jan2074 – Franchise Feb 20 '18 at 20:34
  • try somthing like : `as.character(as.Date(40182, origin = "1899-12-30"), format = "%b-%y")` – MKR Feb 20 '18 at 20:35
  • What is your expected output? – nghauran Feb 20 '18 at 20:35
  • 2
    `format(as.Date.numeric(40182,"1904-01-01"),"%b-%y")` or `format(as.Date(40182,"1904-01-01"),"%b-%y")` – Onyambu Feb 20 '18 at 20:38
  • @MKR this only changes the ouput to chr: "2Jan2074" – Franchise Feb 20 '18 at 20:39
  • Change the origin to match your expectation – MKR Feb 20 '18 at 20:42
  • @Onyambu this is exactly what I needed, now I'll reflect this change over the whole data frame column. Thanks for the help! – Franchise Feb 20 '18 at 20:44
  • See the R Help Desk article in https://www.r-project.org/doc/Rnews/Rnews_2004-1.pdf – G. Grothendieck Feb 20 '18 at 21:28
  • 1
    Possible duplicate of [How to convert Excel date format to proper date with Lubridate](https://stackoverflow.com/questions/43230470/how-to-convert-excel-date-format-to-proper-date-with-lubridate) – lebelinoz Feb 20 '18 at 22:53

2 Answers2

3

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!!!

COLO
  • 1,024
  • 16
  • 28
1
##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.

Michael Vine
  • 335
  • 1
  • 9