4 Replies Latest reply on Feb 12, 2018 10:46 PM by bbregenzer

    Datalink formula error using cell references for dates


      I get an error window when using a cell reference for my start and end dates in the DataLink Sampled Data window.  I have done this successfully in the past, but now it says the formula I typed has an error.  I can type the date directly into the Sample Data box, and it works fine.  Any suggestions?

        • Re: Datalink formula error using cell references for dates

          What date format are you using in Excel? Are you using a relative or absolute time?



            • Re: Datalink formula error using cell references for dates


              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.

            • Re: Datalink formula error using cell references for dates

              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?

              @Brent Bregenzer

              Hi, Dan.

              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.