CODEWITHSUNDEEP
rdateformat
Sumaiya Khatun
Sumaiya Khatun

Reputation: 57

Date changes while importing excel file in R

I have multiple excel files (160) where one 'date' column about 100 observations is in not in proper format. While exporting all the files together the date column changes as follows

Date Column in Excel

Date
05-07-2015
04-07-2015
03-07-2015
02-07-2015
.......

Date column importing in R

Date
42190
42189
42188
42187
......

How to change "42191" to original date format?

Upvotes: 3

Views: 2095

Answers (3)

Bileobio
Bileobio

Reputation: 150

I've found this to be quite helpful:

library(openxlsx)
dates <- c(42190,42189,42188,42187)

datesConverted <- convertToDate(dates); datesConverted
# "2015-07-05" "2015-07-04" "2015-07-03" "2015-07-02"

Gives you exactly what you're looking for.

Upvotes: 0

A. Suliman
A. Suliman

Reputation: 13125

Excel may save dates as numeric or maybe they imported in a numeric format. So you can try:

# from Windows Excel:
as.Date(42190, origin = "1899-12-30")
[1] "2015-07-05"

# from Mac Excel: 
as.Date(42190, origin = "1904-01-01")

Interestingly, Excel support page define the origin date for Windows excel as "1900-01-01", but from here and here you can see that for R, date of "1899-12-30" should use as the origin date.

Upvotes: 1

Saman
Saman

Reputation: 523

copy your date column and special paste it as value in other column and use that to import in R

Upvotes: 0

Related Questions