OSIsoft: Directed Exercise PI and Excel Times. v4.0

Uploaded by OSIsoftLearning on 10.05.2011


Here's a directed exercise in which we asking you
to put in the PI Time abbreviations for each of these
and then also the Excel Time abbreviations. So go ahead and
pause, see if you can do this first, and then let's go over the answers.
Now if you want to just take a look at the solution
and compare your answers, here's what the answers
would be.
Now let's go through them just step by step so you can see
how they work in real time. Now we'll see whether this
works because whatever we put on here as
the PI Time abbreviation is going to show up
as the timestamp for this archive value calculation.
You see I'm making cell references to that.
And anything I put here in the
Excel column will show up as argument down here.
So we'll be able to tell right away whether this works or not, or whether
it's correct or not. So, let's go
about it. First thing, PI Time abbreviation
for "Now" is *. As you can see I've already done
that. I type in *, it gives me the current time which we're
around 11:30 on the 6th of July. Now that would be
done using the "Now" function in Excel
syntax, which gives me roughly the same thing. Now if I
update this we'll see this is going to update at the
same time this other update. So now they match. There we go.
So that's "Now." How about today at midnight? Today
at midnight would simply be the letter "T." As you can see that
evaluates to today at midnight, or over here
I can say equals today, parentheses.
That ends up rendering today at midnight.
Yesterday at midnight would simply be
"Y" for yesterday. Here it would be, well,
there is no "Yesterday" function, so instead
you have to do some math on the "Today" function.
Now Excel makes use of some arithmetic
involving, well, basically, one
is equal to one day. So if I say today minus
one, that should give me yesterday at
midnight. And sure enough it does. There we go.
So, the rule of thumb here is, one,
in Microsoft's Excel syntax,
represents a single day. So, for example, one over
24 would represent one hour.
One, or two, would
represent two days, etc.
Okay. Next step. We've seen
today at midnight, yesterday at midnight, next
step is yesterday at noon. Well that would be handled in
PI syntax this way: Y + 12 hours.
Yesterday at noon. If I did it
in this case, we're going to say today -- what we can do
is today minus, that's today
at midnight, minus how 'bout subtracting
12 over 24. Now, I wrote it that way instead of
just putting in a point five, I did 12 over
24 to make it kind of self-documenting. People can look
at this and say oh, okay, 12 over 24? They
must be talking about hours here. 24 hours in a day,
so that's actually going to end up giving me
yesterday at noon. Note that I
could have just as easily done today minus point
five and gotten the same result.
Next up, how about this morning at 7:30 a.m.?
Well, simple with PI syntax. That's
Today + 7.5H.
You notice you can put
integers here. Excuse me, integers and reals.
So, I can do a real for a half hour
mark. That ends up giving me 7:30. So how would I
do that here? Well, it's essentially the same thing I
did before. Let's see. We wanted Today at
7 in the morning, so we'll say Today + ,
this is going to be 7 over 24,
would be 7 o'clock. Let's do
7.5 over 24. That should give me Today
at 7:30. There we go.
Okay, next up. How 'bout 8 hours ago?
Real simple. * minus 8H,
that's 8 hours back from the current time. Here, we'll
say equals "Now" minus,
now if we did 1 over 24, that would be
one hour, so we'll go with 8 over 24,
that's going to be equal to 8 hours. Ends up
giving us nearly the same result. If I
update this we can see it's exactly the same time.
There we go. That's
"Now" minus 8 over 24.
Two days ago would be * minus
2 days. There we go, 4th of July.
And 2 days ago here would be equal to
"Now", 'cause I said minus
2, each 1 is equal to one day, so that gives me
yesterday, err, 2 days ago.
Monday at 6 a.m.? That's pretty easy with PI syntax.
There's the abbreviation of M-O-N for Monday.
So MON plus 6H is Monday
as of 6 o'clock. You can also have done that
using the full word "Monday." Now there
is no really convenient way of doing that same thing
Monday at 6 o'clock with regard to Excel
Next up is for the first shift yesterday from 6 a.m. in the
morning until 2 in the afternoon. So it's our first shift
from 6 to 2. How would we do that? Well,
yesterday would be "Y." So "Y"
+ 6 hours would be yesterday at
the start time. That's 6 o'clock in the morning.
As you can see it's rendered that as 6.
To go with an end time of
2 p.m., I would simply switch this to "Y" +
14H. That's going to be 2 p.m.
Yesterday. So that would be my start time add
end time. "Y" + 6 to
"Y" + 14H.
Now with Excel time I could do
that same thing. Yesterday at 6 would
be equal to "Today"
minus 1,
and then we'll add -- "Today" minus
1 would be yesterday -- + 6 over 24.
Again, I like writing it like that because it's
kind of self-documenting. You can see we're talking about 6 a.m.
Because we're subtracting the one day,
adding six hours. I think that's a little easier than just
subtracting, say, 18 hours from today.
For the end time,
"Today" minus 1 +, let's go with 14
hours over 24, that's 2 p.m.
And, finally, what's the
last 7 hours? That would be * minus
7 hours, that would be the
start time. The end time would simply be *
by itself. And over on Excel, that would be
equal to "Now" minus,
again, 7 over 24.
That's 7 hours.
And just remove the
7 over 24 to do
equals the current time "Now."