CODEWITHSUNDEEP
r
rachel394
rachel394

Reputation: 21

how can I convert number to date?

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!

Upvotes: 2

Views: 15837

Answers (4)

Mehmet Yildirim
Mehmet Yildirim

Reputation: 501

You can use convertToDateTime function from openxlsx package which is also a great package for importing and exporting the dataframes.

convertToDateTime(43324, origin = "1900-01-01")

If your date does not make sense, try changing the origin. There are couple different dates, such as "1970-01-01", "1899-12-30", etc.

Upvotes: 0

davedgd
davedgd

Reputation: 454

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"

Upvotes: 1

selfawarelemon
selfawarelemon

Reputation: 77

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")

Upvotes: 2

Will
Will

Reputation: 1313

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"

Upvotes: 1

Related Questions