Dates in Excel's internal format #266

Closed
pitakakariki opened this Issue Feb 14, 2017 · 1 comment

Comments

2 participants
@pitakakariki

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

These should map to the first, second, and third of January 2008.

> 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")
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'
> z
  Date
1 <NA>
2 <NA>
3 <NA>

@jennybc jennybc added this to TODO in jennybc Feb 26, 2017

@jennybc

This comment has been minimized.

Show comment
Hide comment
@jennybc

jennybc Mar 2, 2017

Member

I have a column which has dates in Excel's internal format.

So this isn't quite true. Generally, a date cell in xlsx will carry a date style, which these cells do not. They are just integers and it is a choice to interpret them as dates. But I suppose we can interpret numbers as dates, when explicitly directed to do so via col_types = "date".

Member

jennybc commented Mar 2, 2017

I have a column which has dates in Excel's internal format.

So this isn't quite true. Generally, a date cell in xlsx will carry a date style, which these cells do not. They are just integers and it is a choice to interpret them as dates. But I suppose we can interpret numbers as dates, when explicitly directed to do so via col_types = "date".

@jennybc jennybc closed this in #277 Mar 5, 2017

jennybc added a commit that referenced this issue Mar 5, 2017

Add logical cell & col types; refactor cell typing and coercion (#277)
* Add logical cell and col types; general refactor of cell typing and coercion

Rescues xls formula dates

* Make logicalFromString() to use in xls and xlsx

* Attempt to coerce text to number; fixes #217, fixes #106

* Work on NEWS.md

* Fiddle with comments and kick appveyor

* Edit Xls[x]WorkSheet.h side-by-side for parallelism

* Edit NEWS.md

* Coerce numeric to date in a "date" column; fixes #266

* Don't use C++11 when converting string to double

* Test all the warnings

* Enhance comments and refactor xls cell typing

* Consistently use strncmp(this, that, n) == 0

* Simplify logical cell creation in xls

* Fewer parens

* Add comment, collapse two cases

* Use Rf_StringTrue() and Rf_StringFalse()

* Beef up tests of logical coercion; delete old coercion test that adds nothing

* Frontload comments re: xls & xlsx cell types and make more parallel

@jennybc jennybc moved this from TODO to Done in jennybc Mar 10, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment