OSIsoft: Describe Excel time format and choose between it and PI time format. v4.0

Uploaded by OSIsoftLearning on 10.05.2011


When working with DataLink you've got your choice of what
timestamps you're going to use. You can use the PI timestamps
that you see right here, this time syntax, where
you can specify -- oh, let's go back
say * minus one hour
versus * minus 2 days. You know, this
syntax, Y for yesterday, Y + 7 hours,
to today + 7 hours -- this syntax is
completely available to you from, with, wherever you're working
with PI data, whether it's process book DataLink, etc.
But, because this is Excel, you also have the option
of using these Excel functions. Like, for example, you
see this "Equals Now." I can put this anywhere that I use
this same type -- or this, the time
syntax. So for example, I can say equals today
and that's today at midnight. I can
say equals yesterday -- excuse me, equals
today minus 1, there is no yesterday.
There we go. Today minus 1 is exactly
1 day ago. We measure time in days
in Excel. So, and for example, today,
oh, let's say today plus
7 over 24, that would be today at
7 o'clock in the morning, to, say the current time equals now.
So, all of
these are options available to you because
we're working within Excel, and Excel
supports those types of formats. Now, there's advantages
to both. One of the advantages of using this, the "Today
Now" types of functions, is that
every time you press F9 or
even just close and open the workbook, that's going
to update these with new information and that will in
turn, do a full calculate of everything that is based on that.
So, just, you know if I were to save this,
do a File Save, Close and Open, it's going to show me the current day.
Now, that's not the case if I were to do something like this.
If I were to go from Yesterday + 7 hours
to Today + 7 hours, and of course
as I edit this, this gives me the right dates
but if I were to do a File, Save, and come back
in a couple of days, close and open this, it's still going to show me the 5th of
July. It's not going to update. That's one of
the advantages of using, if I were to say,
use the "Equals Today" function instead.
And of course, the format that you use
here, if you are using Excel syntax,
the format is going to be whatever format is
supported on your copy of Windows. And that's handled
under "Control Panel, Regional Settings" --
if you go into your "Regional Settings" under
"Customize", this is where you would set your time and date formats.
So we're going to, we're going to recognize all those
formats that you're using, you know, by default, with Windows.
A couple more things we haven't looked at yet. Within the
PI menu, you'll notice that there is a "Connections"
dialog. This is the same "Connections" dialog box that we covered
earlier in class, so this is where we would add
new servers, control the default server, that type of thing.
We also support a tag search from here.
It's the same tag search dialog we've see with other
applications. Let me scroll over here and I'll give you an example.
If I do a "Tag Search", and I'll go ahead and
reset this. Let's look for all the tags that begin with the letter
"B". I go ahead and search. I
found all these tags. I can select what I want, click on
"OK", and we're going to drop those down in here.
So that's the "Tag Search" function.
Now since we're talking about tag search, be a good time to talk just briefly
about cell references. If you notice,
this DataLink function requires
the name of a PI tag. Now I did
not type, you know, hard code, the name of a PI
tag, in here. I could have done that. CDT158.
8 for example is the name of a PI tag.
Oop, that's right, I have to do Control-Shift
-Enter to change this array. Now, there we go.
Now, by putting that in like that,
hard-coded, now,
you know, there's no longer the cell reference to the tag name,
and without that cell reference it basically
means that you're stuck with whatever you first chose.
And, if we go back and we take a look at this, as you can
see, it's actually put in the name of the tag.
Okay. So when you first create this function,
generate this function using the "Dialog" box,
you can actually put in the name of a tag. I
would suggest, instead,
that you make a cell reference. So I'll go ahead and make a cell reference to that
and, of course, the beauty of this is, once you've made
that cell reference, now all you need to do is change
this to different PI tags and you'll get different
results. So it just makes it a little bit more flexible.
It's up to you. Of course, it doesn't matter how you
do this. The data's going to come out the same. It's just a matter of
flexibility. So, all of these in this case, and
in most of this class you're going to see cell references.
If we go over and we take a look at some of our
here's some of the samples. You know, for example, if I change this
one cell reference to
"Level" instead of "Temperature", now we see all those things
that are based on that cell reference change in response,
because we're making cell references, we're not hard
coding the names of PI tags. Now,
what I just said about cell references to tag
names, it's the same when it comes to "Start Time" and "End
Time." You'll notice here I've made cell references to
the "Start Time" and "End Time". Again, I could have,
when I first generated this function, I could have hard coded some
"Start Times" and "End Times", but I chose not to.
And, so now, you know, as you've seen me
demonstrate if I were to go ahead and change this
to something else, that automatically updates.