yves.lesperance

Excel VLOOKUP on dates returned by PI Datalink

Discussion created by yves.lesperance on Nov 11, 2014
Latest reply on Nov 11, 2014 by Gregor
Hi ! I have many excel reports that uses PI Sampled Data and PI Compressed Date. These reports uses a date generated with Excel and a VLOOKUP to go through the returned values.   For some reasons, this stopped working on November 10th at 00:00.  It looks like Excel has trouble comparing dates generated in it and returned from PI Datalink.  Excel always had trouble with floating point.  However, what I cannot explain, is why it always worked before November 10th at 00h00.  Now it works 1/3 of the time, which is on the hours where it needs only 3 decimals.  Anyone else have that problem ? What is the solution?  I prefer solution where I don't have to modify all the excel reports everyone built... I tried attaching a test file with Chrome and IE and both failed. I'll try later. For those willing to test anyway and see for themselves : I generate the dates by putting 2014-11-09 00:00 in Cell A1.  Cell A2 contains =(A1*24+1)/24 and I drag down to generate two days of timestamps.  I get some values from PI using an hourly PI Sampled date for the same time span.  Note that dates returned at least appear to be the same.  Then I do =VLOOKUP(A1;$B$1:$C48;2;FALSE) in D1 and drag down. and it works on and off since today...

Outcomes