'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?
Solution 1:[1]
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.
Solution 2:[2]
copy your date column and special paste it as value in other column and use that to import in R
Solution 3:[3]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Saman |
| Solution 3 | Leonor |