Uploaded by OSIsoftLearning on 10.05.2011

Transcript:

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

format.

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."