sdllc
The way BERT handles dates
Dear BERT team,
First of all thanks for making this great add-in. I do however have a question regarding the way BERT reads in dates from Excel. The BERT console reads the dates as a numeric (as if the origin is "1899-12-30"). Using as.Date(..., origin = "1899-12-30") inside the R-scripts is no optimal solution. Indeed R will internally convert the numeric to the correct date, but when it later on returns this date to Excel (this will be done in the form of a numeric again) it will assume the origin is "1970-01-01". When we adapt the format of this numeric output in Excel to 'short date' we will have a date which differs a lot from the one we initially entered.
The following function gives you a better idea of what I mean:
testdates = function(Date){ Date = as.Date(Date, origin = "1899-12-30") print(Date) print(as.numeric(Date)) return(Date) }
Are you guys thinking of changing anything to the way dates are handled with BERT? I know the xlwings add-in (for interaction between Excel and Python) for example is very user friendly regarding dates.
The way I interpret this the problem originates from the fact that BERT reads dates as numerics and since R and Excel have different origins this gives some issues.
Kind regards,
Arno
So the problem is that dates in Excel are numbers. In Excel's internal data structure (XLOPER/XLOPER12) there is no distinction made for dates. And as you note Excel does not use the Unix epoch so translating them is a pain (I seem to recall some issues around leap years as well).
I think the only way to know that something is a date in Excel is to look at cell formatting. However we really don't want to have to do that on every function call, since it will just slow down functions. (I'm going to check that assertion, though).
The story is a little different in VBA, because the basic COM data structure (VARIANT) does have a date type, and Excel uses it (although it's the same numeric value). We had some earlier issues because we were not aware of that.
When sending data from R -> Excel we could probably do the translation without much trouble. So the only problem is knowing when an Excel value is a date.
As an aside, Excel doesn't have complex numbers either -- it uses strings. At the moment we support complex from R -> Excel but not vice-versa, again because we don't want to have to test every time.
So would it be possible then to add functionality so that when BERT returns dates from R -> Excel these are returned as numerics with origin "1899-12-30" instead of "1970-01-01"? In that case it would be sufficient to use as.Date(..., origin = "1970-01-01") within the function to properly handle dates.
One reason for the confusion is that the 'origin' of the date system needs to be considered in light of the fact that Excel is emulating a Lotus 123 bug that considered 1900 to be a leap year. See https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/
