r - Convert Excel numeric to date

Question

I have a vector of numeric excel dates i.e.

date <- c(42963,42994,42903,42933,42964)

The output am I expecting when using excel_numeric_to_date function from janitor package and as.yearmon function from zoo package

as.yearmon(excel_numeric_to_date(date)) [1] "Aug 2016" "Sep 2016" "Jun 2017" "Jul 2017" "Aug 2017".

However, the conversion for the first to elements of the date vector are incorrect. The actual result are:

as.yearmon(excel_numeric_to_date(date)) [1] "Aug 2017" "Sep 2017" "Jun 2017" "Jul 2017" "Aug 2017"

I have tried using different option(modern and mac pre-2011) for the date_system argument in the excel_numeric_to_date but it does not help either

The excel version is 2010

Answer 1

You can simply use as.Date and specify the origin, i.e.

as.Date(date, origin="1899-12-30") 
#[1] "2017-08-16" "2017-09-16" "2017-06-17" "2017-07-17" "2017-08-17"

#or format it to your liking,

format(as.Date(date, origin="1899-12-30"), '%b %Y') 
#[1] "Aug 2017" "Sep 2017" "Jun 2017" "Jul 2017" "Aug 2017"

This link gives quite a bit of information on this matter.