CODEWITHSUNDEEP
rexceldate
tanee
tanee

Reputation: 55

Reading dates from Excel

I am having two date(formatted as dd-mm-yyyy in excel) columns in my data in excel sheet. 

Date Delivery   Date Collection
06-08-17    15-08-17
11-04-17    15-04-17
24-01-17    24-01-17
11-08-16    14-08-16

There are multiple issues. Currently I am reading a subset of data(manually made of top 100 rows in another excel sheet.).

  1. The dates in same format in excel are shown differently in R.

  2. They all look like as in Data.Collection when I read the whole data set.

    data <- read.xlsx("file.xlsx", sheetName='subset', startRow=1)

The data output shown in R is

Date formats in R.

I need them all to be shown as in Data.Delivery because I need to write the result back after analysis.

I am also trying to make it Date in R using

dates <- data$Date.Delivery
as.Date(dates, origin = "30-12-1899",format="%d-%m-%y")

Upvotes: 0

Views: 2528

Answers (2)

Sophia J
Sophia J

Reputation: 105

or you can read the file using "gdata" or "XLConnect" packages to read the column as factor. then use ymd() from lubridate to convert it into date

require(gdata) data = read.xls (path, sheet = 1, header = TRUE) data$Date.Collection <- ymd(data$Date.Collection)

Upvotes: 0

nghauran
nghauran

Reputation: 6778

To format Date.Collection as in Data.Delivery after reading your file, try

# see the str of your data
str(data)
# if Date.Collection is characher
data$Date.Collection <- as.numeric(data$Date.Collection)
# if Date.Collection is factor
data$Date.Collection <- as.numeric(levels(data$Date.Collection))[data$Date.Collection]
# conversion
data$Date.Collection <- as.Date(data$Date.Collection - 25569, origin = "1970-01-01")

Upvotes: 2

Related Questions