Pablo Benvenuto

Learning Lab - PI and Excel Times Part 1

Discussion created by Pablo Benvenuto Champion on Sep 7, 2012
Latest reply on Jul 30, 2013 by Pablo Benvenuto
This is a short Learning Lab to help you become acquainted with the differences and similarities between PI and Excel time notations.

You can follow this lab by using your production environment or you can obtain a subscription to the OSIsoft Virtual Learning Environment, which allows you to work hands on in any of the Learning Labs published in this community.

We will start with a template in Excel that will allow you to compare PI and Excel times side by side.  The template can be downloaded from here and it looks like this:


The spreadsheet contains a number of ranges and points in time listed in column A, and we are going to represent them with PI and Excel notation.  First, we will retrieve a PI Tag value using the Archive Value function using the PI time on one column and using the Excel time on a different column.  We will then compare the two notations by comparing the time stamps associated with the two values retrieved.

Relative times

We will start with the simplest of PI Times, which is now and represented by *.  If you are using the excel template, simply type * right next to "Now (current time)" and under the "PI Times Abbreviation" column in cell B6.  We will now place the PI Tag value and its timestamp right next to it.  Select cell C6 and click on PI > Archive Value.  in the function dialog window, select the Tagname field and click on cell B1, where the name of the tag CDT158 is contained.  Select the Timestamp field and click on cell B6 where * is.  At last select the time at left option from the dialog window and click on OK.  A timestamp will be displayed on cell C6 and its corresponding value on cell D6.

Now we can repeat the same process to retrieve the same value with Excel time.  The corresponding function in Excel is NOW().  So we can type =NOW() in cell E6 , and we can see that Excel immediately displays the current time.  We can select cell F6, click on the Archive Value function and select cell B2 for the Tagname and cell F6 for the Timestamp.  We select the time at left option and click on OK.  The timestamp and value retrieved will be displayed on cell F6 and G6 respectively.


Because now is a relative time, the two timestamps in cells C6 and F6 may not exactly match right now, because they were retrieved at different times.  What we need to do now is update or recalculate the spreadsheet so that the value is retrieved at the same time.  Interestingly and as a side note, F9 will only recalculate volatile functions in Excel, such as NOW(), but not Archive Value which is not a volatile function.  In order to recalculate all functions on the spreadsheet at the same time we can press CTRL + ALT + F9.  This combination will recalculate all functions, retrieving both values at the same time.  Comparing the timestamps we can see that * and NOW() produce the same result with the same timestamp.

The next time in the list is 8 hours ago, which can be represented as *-8h in PI.  Since Excel stores dates as whole number of days starting at 1900, dates and times are values and therefore can be added, subtracted, and included in other calculations.  We can write =NOW()-(8/24).  We can now follow the same process as before to retrieve the PI Tag value.  Select cell C7 and click on Archive Value.  When the dialog window opens, select B2 as the Tagname and B7 as the Timestamp.  Select the time at left option and click OK.

Now select cell F7, click Archive Value and make the same selections as before in the dialog window, using the Excel time as Timestamp.  Once again we will have to recalculate the spreadsheet to compare these relative times.  Pressing CTRL + ALT + F9 will show that these expressions are equivalent because the timestamps and the values retrieved are the same.

For the last example of a relavtive time we have 2 days ago.  We can simply represent this as *-2d in PI notation and as =NOW()-2 in Excel notation.  Every unit in Excel represents a day.  We can now retrive the corresponding Archive Value and place the timestamps in cells C8 and F8.  Recalculating we see that the times match and the spreadsheet now looks like the picture below.


Good job making it this far, you are in a roll.  So don't waste any time and go to Part 2 of this Learning Lab.