pditty
December 2, 2017, 5:56pm
21
Yeah. At work excel is everywhere, and even if the data have gone from that to .csv, I often find dates columns as numeric columns once it gets into R. I've been using something like as.Date() with an origin argument (that I always have to google b/c I forget the date that goes in there), so this function will be great to have.
1 Like
Yeah, Excel dates as numbers are a nightmare. Here is what I use
df$date <- as.character(as.Date(as.POSIXct((as.numeric(as.character(df$date)) * 86400),
origin = "1899-12-30", tz = "GMT")))
1 Like
I don't know whether that 5 * as. is a thing of horror or strange beauty ...
1 Like
pditty:
Yeah. At work excel is everywhere, and even if the data have gone from that to .csv, I often find dates columns as numeric columns once it gets into R. I’ve been using something like as.Date() with an origin argument (that I always have to google b/c I forget the date that goes in there), so this function will be great to have.
@pditty what pkg/how are you using to read the data in? readxl has that functionality upfront, so I would presume you wouldn't truly need such a heavy workaround:
opened 01:14AM - 14 Feb 17 UTC
closed 08:47PM - 05 Mar 17 UTC
bug
datetime
I have a column which has dates in Excel's internal format. The Excel file comes… from someone's reporting software, so I can't just format the column in Excel (unless I want to do it every time I get an update).
◢ |Date
-|-
1|39448
2|39449
3|39450
[date_test.xlsx](https://github.com/tidyverse/readxl/files/772728/date_test.xlsx)
These should map to the first, second, and third of January 2008.
```{r}
> as.Date(read_excel("../notes/date_test.xlsx")$Date, origin="1899-12-30")
[1] "2008-01-01" "2008-01-02" "2008-01-03"
```
But if I tell `read_excel` that it's a date:
z <- read_excel("../notes/date_test.xlsx", col_types="date")
```{r}
Warning messages:
1: In read_xlsx_(path, sheet, col_names = col_names, col_types = col_types, :
[2, 1]: expecting date: got '39448'
2: In read_xlsx_(path, sheet, col_names = col_names, col_types = col_types, :
[3, 1]: expecting date: got '39449'
3: In read_xlsx_(path, sheet, col_names = col_names, col_types = col_types, :
[4, 1]: expecting date: got '39450'
```
```{r}
> z
Date
1 <NA>
2 <NA>
3 <NA>
```
