I am having trouble understanding of how dates are handled.
I understand that Excel handles dates differently from R. I am fine with reading in a date as a numerical value via R functions. For example, in a workbook sheet "Sheet1" cell A1 I can input a date (for example 11Apr2018) as either a number 43201, any date format such as 4/11/2018 or 11-Apr-18, or formatted as text.
In my functions.R script I can save a function:
test_date1 <- function(x){
print(str(x))
return(x)
}
When I call this function in excel =R.test_date1(A1), R reads this as a numeric variable. If I have a range of dates in A1:A2 and use above function then I get a vector of numbers. I am happy with this and can convert to R dates using as.Date(x, origin="1899-12-30", tz='UTC').
Now when I try to use scripting in BERT, I cannot read in dates when they are not formatted as values. For example, if cell A1 is formatted as a number I i execute below script:
test_value <- EXCEL$Application$get_Sheets()$get_Item('Sheet1')$get_Range('A1')$get_Value()
My test_date1 variable hold a number and I again can convert to an R date. But when A1 is formatted as a date in excel the same script returns a NULL value.
Ultimately I would like to be able to reference ranges in excel and use time series for ggplot2 plotting and for statistical analysis. But I am having problems handling dates.
