OSIsoft: Use Excel's dynamic time functions with PI DataLink. v3.1

Uploaded by OSIsoftLearning on 13.06.2011


We should make one note about Time
Functions in Excel.
Most people are familiar with PI Client Tools
or System Manager Tools and our Time Abbreviations.
Those would the the * for Now, and the T
for Today, and 1 if for Yesterday, and the offsets that we
have. So, for example,
*-2H would be Now minus two hours or
2 hours ago. It's important to realize that
Excel has Time Functions
as well, Dynamic Time Functions, and
while both the PI Time Abbreviations
and the Excel Time Abbreviations will both function
correctly, either as entered into dialog boxes or
when used as cell references, it's
often preferable to use the Excel Functions
for your reports. And the reason is
very simple. Here's an example
where I have the PI Time Formats of T
for Today; Y for Yesterday --
which means Yesterday at midnight; the *, which means
Now; and * minus 4H, which means Now
minus 4 hours ago. And, in Column D, I
have the Excel equivalents. You can see this is
Today () is the equivalent of
T. The equivalent of yesterday is TODAY()
minus one. The equivalent of Now
is Equals Now(), and
Now minus 4 hours is Now()
minus 4 over 24. The big
difference in the formats is that Excel bases everything
on a Unit of Days,
so if you want to go back four hours, you have to say minus four over
24, as opposed to 4H.
And the reason we prefer people to use Excel Time
Functions as opposed to the PI Time Functions,
is that
in the Excel Time Function,
you can actually see the date and time, and
this is really important because Excel
does not have a natively updating function
like ProcessBook does, so if you
update your screen and then you walk away for 20 minutes
and come back and look at your report, you are
not really sure when that ran if you use
the PI Time Formats because
there may not be a, a, a date and time on
there of when the report ran. However, if you
use the Excel Time Formats,
and you run a report, you, you update the spreadsheet,
that date and time is on the
spreadsheet. So, if you have a Start Time and an End Time for your
Array of Values, that Start Time and End Time
will be there at the heading of your worksheet. So,
we often do prefer you use the Excel
notations as opposed to PI notations
and either will work, either entered into the dialog box
or use the cell references.