0

I have a problem with the as.date function. I have a list of normal date shows in the excel, but when I import it in R, it becomes numbers, like 33584. I understand that it counts since a specific day. I want to set up my date in the form of "dd-mm-yy".

The original data is: how the "date" variable looks like in r

I've tried:

as.date <- function(x, origin = getOption(date.origin)){
  origin <- ifelse(is.null(origin), "1900-01-01", origin)
  as.Date(date, origin)
}

and also simply as.Date(43324, origin = "1900-01-01") but none of them works. it shows the error: do not know how to convert '.' to class “Date”

Thank you guys!

rachel394
  • 1
  • 2
  • (1) `ifelse` is wrong here, use `if (is.null(origin)) origin <- "1900-01-01"`. (2) I don't get that error with that code. – r2evans Oct 23 '20 at 16:59
  • 1
    BTW, another way to deal with empty origin is `function(x, origin = getOption(date.origin, "1900-01-01"))` – r2evans Oct 23 '20 at 17:00
  • @r2evans, thank you, that helps! But I'm also having troubles on converting a list of date numbers to the normal date format. Like I have a "mydate" variable with 100 dates, and I want to convert all of them, but I don't know where and when should I introduce the date origin. – rachel394 Oct 23 '20 at 20:21
  • I can't help any more without sample data. Please provide sample data using `dput(..)` or `data.frame(..)`; good references for making the question complete and reproducible: https://stackoverflow.com/q/5963269, [mcve], and https://stackoverflow.com/tags/r/info. – r2evans Oct 23 '20 at 20:31

3 Answers3

1

The janitor package has a pair of functions designed to deal with reading Excel dates in R. See the following links for usage examples:

janitor::excel_numeric_to_date(43324)
[1] "2018-08-12"
davedgd
  • 387
  • 2
  • 6
0

I've come across excel sheets read in with readxl::read_xls() that read date columns in as strings like "43488" (especially when there is a cell somewhere else that has a non-date value). I use

xldate<- function(x) {
  xn <- as.numeric(x)
  x <- as.Date(xn, origin="1899-12-30")
}

d <- data.frame(date=c("43488"))
d$actual_date <- xldate(d$date)
print(d$actual_date)
# [1] "2019-01-23"
Will
  • 1,206
  • 9
  • 22
0

Dates are notoriously annoying. I would highly recommend the lubridate package for dealing with them. https://lubridate.tidyverse.org/

Use as_date() from lubridate to read numeric dates if you need to.

You can use format() to put it in dd-mm-yy.

library(lubridate)

date_vector <- as_date(c(33584, 33585), origin = lubridate::origin)

formatted_date_vector <- format(date_vector, "%d-%m-%y")
selfawarelemon
  • 57
  • 6