Pablo Benvenuto

Learning Lab - PI and Excel Times Part 2

Discussion created by Pablo Benvenuto Champion on Sep 12, 2012

It is so good to see you back, just in time for this second part of this Lab. Hopefully, you got all your questions answered on Part 1 of this Learning Lab. If not, you can always post your questions below. Let's keep going.

Absolute times

The next examples are absolute times or at least not quite as relative as the examples above, so we will not have a need to recalculate the spreadsheet. The first example is today at midnight, which can easily be represented as t in PI notation and =today() in Excel notation. Once again we will retrive the archive value at this time using both notations and the Archive Value function in PI DataLink. Make sure the corresponding timestamps are retrieved to cells C9 and F9 respectively.

The next example is yesterday at midnight, which can be represented as y in PI and =today()-1 in Excel. Place each syntax in cells B10 and E10 respectively. Ensure the retrieved timestamps are displayed in cells C10 and F10 respectively.

Yesterday at noon can be represented as y+12h or t-12h in PI. Since there is no yesterday in Excel, the easiest way to write this is as =today()-(12/24). Now follow the same steps as before to retrieve the PI Tag values and timestamps. Similarly, this morning at 7:30 am can be represented as t+7.5h in PI and as =today()+(7.5/24) in Excel.

At last, monday at 6 am can be represented as m+6h in PI. However, there is no simple way to write a simliar notation in Excel.

Time Ranges

Often times we are interested in production data over a specific range of time. The PI System is especially suited to retrieve production data, no matter how long ago or how large the range is. In the first example we have a range of time with a start time of the first shift yesterday at 6 in the morning. We can select cell B15 and type y+6h. The end time for this shift is 2 in the afternoon. You can select cell B16 and type y+14h.

We can express the time range in Excel time by typing =today()-1+(6/24) as the start time and =today()-1+(14/24) as the end time in cells E15 and E16 respectively.

The last time range should be very easy for you to figure out. So go ahead, retrieve values for the last time range and compare the timestams. The completed exercise should look like the picture below.



You can watch this Learning Lab in video format below or download it from here. We also have an online PI DataLink Basic workshop. Check out the Workshop Outline. Lastly you can also go to the YouTube OSIsoft Learning Channel and do a search for PI DataLink to find complete playlists on this topic.