What date format are you using in Excel? Are you using a relative or absolute time?
I finally resolved my issue by inserting a new row, entering my desired dates in the new cells, and then using those new locations for my cell references in the Sample Data window. Apparently, there was some residual formatting (or something) in the cells I was trying to use, even though I made sure they were formatted as Date. I then deleted the original row I was using, and my sheet was good-to-go! Frustrating when there is no obvious or easily fixed reason for a common task not to work.
This EXACT issue happens to me all the time, except that making new rows does NOT fix it.
-Build datalink PI calc with absolute date cell references
-sheet runs fine
-time passes, suddenly existing calc returns error that "there's an error in the formula you entered", and it wipes out that calculated data cell.
This spreadsheet is full of PI calculations using that same time stamp (eg 12/1/2017) that STILL work after this error occurs on a different calc.
However - once the error occurs for the first time - that spreadsheet is now unable to add ANY new PI calculations. (but the existing ones continue to work)
Manually typing in the date DOES work - but I can't use the spreadsheet in that manner. Please help.
I think I found my answer scouring this website. My filename was too long?
Past cases indicate that you could be running into a character limit for formula arrays in Excel: https://support.microsoft.com/en-us/kb/213181
There is a 255 character limit so you can try a few things:
Shortening the name of the sheet
Shortening the name of the file
If this doesn't help then perhaps you could post some screen shots or a cleansed copy of the Excel file.
I'm glad you were able to find a helpful answer here on PI Square. For those who are interested, here is a link to the fuller discussion, which will provide some extra context: